declare @varTemp binary(5) declare @strRe varchar(20) declare @num int declare @vartempint bigint set @varTemp =0x0123456789 set @vartempint = cast(@varTemp as bigint) set @strRe='' Set @num =2 while(@vartempint>0 or @num >0) begin set @strRe = cast(@vartempint%16 as varchar)+@strRe set @vartempint = @vartempint /16 if @vartempint = 0 set @num = @num -1 end select @strRe
感谢楼上各位的帮助,尤其是yibey,多谢多谢。下面是我的改进,希望对想处理类似问题的朋友有所帮助。 CREATE FUNCTION BIN2STR(@VARTEMP BINARY(6)) RETURNS VARCHAR(20) BEGIN DECLARE @BINARY_LEN INT,@RETSTR VARCHAR(40),@STRRE VARCHAR(20) SET @BINARY_LEN = 6 SET @STRRE='' SET @RETSTR='' IF @VARTEMP IS NULL BEGIN SET @VARTEMP = 0x0 WHILE (LEN(@STRRE) < @BINARY_LEN * 2) BEGIN SET @STRRE = '0' + @STRRE END END ELSE BEGIN DECLARE @VARTEMPINT BIGINT,@TMP_INT INT,@TMP_CHR VARCHAR(2) SET @VARTEMPINT = CONVERT(BIGINT,@VARTEMP) WHILE(@VARTEMPINT > 0) BEGIN SET @TMP_INT = @VARTEMPINT%16 SET @TMP_CHR = CASE @TMP_INT WHEN 10 THEN 'A' WHEN 11 THEN 'B' WHEN 12 THEN 'C' WHEN 13 THEN 'D' WHEN 14 THEN 'E' WHEN 15 THEN 'F' ELSE CONVERT(VARCHAR(2),@TMP_INT) END SET @STRRE = @TMP_CHR + @STRRE SET @VARTEMPINT = @VARTEMPINT /16 END END WHILE(LEN(@STRRE) < @BINARY_LEN * 2) BEGIN SET @STRRE = '0' + @STRRE END --将字符串转为MAC格式 WHILE(LEN(@STRRE) > 0) BEGIN IF(LEN(@STRRE) = 2) BEGIN SET @RETSTR = @RETSTR + SUBSTRING(@STRRE,1,2) END ELSE BEGIN SET @RETSTR = @RETSTR + SUBSTRING(@STRRE,1,2) + ':' END SET @STRRE = SUBSTRING(@STRRE,2 + 1,LEN(@STRRE) - 2) END RETURN(@RETSTR) ENDSELECT DBO.BIN2STR(0X05689000000)
select right('0x0123456789',len('0x0123456789')-2)
len('0x0123456789') - charindex('x', '0x0123456789'))
set @str = '0x0123456789'
select replace(@str,'0x','')
set @str = '0x0123456789'
select replace(@str,'0x','')
declare @varTemp binary(6)
set @varTemp = 0x0123456789
select @varTemp
如何将这个@varTemp的值转换为0123456789
-----
#Eg(1 行受影响)
但是我想要的是0123456789
select replace(0x0123456789,0x,'')
都是乱码,原来是有难度的啊
declare @varTemp binary(5)
declare @strRe varchar(20)
declare @num int
declare @vartempint bigint
set @varTemp =0x0123456789
set @vartempint = cast(@varTemp as bigint)
set @strRe=''
Set @num =2
while(@vartempint>0 or @num >0)
begin
set @strRe = cast(@vartempint%16 as varchar)+@strRe
set @vartempint = @vartempint /16
if @vartempint = 0
set @num = @num -1
end
select @strRe
CREATE FUNCTION BIN2STR(@VARTEMP BINARY(6))
RETURNS VARCHAR(20)
BEGIN
DECLARE @BINARY_LEN INT,@RETSTR VARCHAR(40),@STRRE VARCHAR(20)
SET @BINARY_LEN = 6
SET @STRRE=''
SET @RETSTR=''
IF @VARTEMP IS NULL
BEGIN
SET @VARTEMP = 0x0
WHILE (LEN(@STRRE) < @BINARY_LEN * 2)
BEGIN
SET @STRRE = '0' + @STRRE
END
END
ELSE
BEGIN
DECLARE @VARTEMPINT BIGINT,@TMP_INT INT,@TMP_CHR VARCHAR(2)
SET @VARTEMPINT = CONVERT(BIGINT,@VARTEMP)
WHILE(@VARTEMPINT > 0)
BEGIN
SET @TMP_INT = @VARTEMPINT%16
SET @TMP_CHR = CASE @TMP_INT WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN 'D'
WHEN 14 THEN 'E'
WHEN 15 THEN 'F'
ELSE CONVERT(VARCHAR(2),@TMP_INT) END
SET @STRRE = @TMP_CHR + @STRRE
SET @VARTEMPINT = @VARTEMPINT /16
END
END
WHILE(LEN(@STRRE) < @BINARY_LEN * 2)
BEGIN
SET @STRRE = '0' + @STRRE
END
--将字符串转为MAC格式
WHILE(LEN(@STRRE) > 0)
BEGIN
IF(LEN(@STRRE) = 2)
BEGIN
SET @RETSTR = @RETSTR + SUBSTRING(@STRRE,1,2)
END
ELSE
BEGIN
SET @RETSTR = @RETSTR + SUBSTRING(@STRRE,1,2) + ':'
END
SET @STRRE = SUBSTRING(@STRRE,2 + 1,LEN(@STRRE) - 2)
END
RETURN(@RETSTR)
ENDSELECT DBO.BIN2STR(0X05689000000)