--建测试表 create table fy (id int not null, bcol binary(5) not null constraint pk_fy primary key(id) )insert into fy select 1,convert(binary(5),getdate()) union all select 2,convert(binary(5),4)select id,bcol from fyid bcol ----------- ------------ 1 0x26009DAD1F 2 0x0000000004--问题重现 select id,cast(bcol as varchar) bcol from fyid bcol ----------- ------------------------------ 1 & 澀 2 select id,convert(varchar,bcol) bcol from fyid bcol ----------- ------------------------------ 1 & 澀 2 --解决方法 --1.建函数ConvertVarBinary_HexStr create function dbo.ConvertVarBinary_HexStr ( @bin varbinary(1000)) returns varchar(1000) as begin DECLARE @Return varchar(1000), @ind int, @byte binary(1),@byte1 int, @byte2 int; SELECT @Return = '',@ind = 1;
WHILE ( @ind <= datalength(@bin) ) BEGIN SELECT @byte = substring(@bin, @ind, 1); SET @byte1 = @byte / 16 IF(@byte1 >= 10) SELECT @Return = @Return + CASE @byte1 WHEN 10 THEN 'A' WHEN 11 THEN 'B' WHEN 12 THEN 'C' WHEN 13 THEN 'D' WHEN 14 THEN 'E' WHEN 15 THEN 'F' END ELSE SELECT @Return = @Return + convert(char(1),@byte1) SET @byte2 = @byte % 16 IF(@byte2 >= 10) SELECT @Return = @Return + CASE @byte2 WHEN 10 THEN 'A' WHEN 11 THEN 'B' WHEN 12 THEN 'C' WHEN 13 THEN 'D' WHEN 14 THEN 'E' WHEN 15 THEN 'F' END ELSE SELECT @Return = @Return + convert(char(1),@byte2) SELECT @ind = @ind + 1; END RETURN @Return; end--2.使用函数ConvertVarBinary_HexStr转换binary列值 select id,dbo.ConvertVarBinary_HexStr(bcol) 'bcol' from fy--结果(已是varchar类型). id bcol ----------- ------------ 1 26009DAD1F 2 0000000004(2 row(s) affected)
--建测试表
create table fy
(id int not null,
bcol binary(5) not null
constraint pk_fy primary key(id)
)insert into fy
select 1,convert(binary(5),getdate()) union all
select 2,convert(binary(5),4)select id,bcol from fyid bcol
----------- ------------
1 0x26009DAD1F
2 0x0000000004--问题重现
select id,cast(bcol as varchar) bcol from fyid bcol
----------- ------------------------------
1 & 澀
2 select id,convert(varchar,bcol) bcol from fyid bcol
----------- ------------------------------
1 & 澀
2
--解决方法
--1.建函数ConvertVarBinary_HexStr
create function dbo.ConvertVarBinary_HexStr
( @bin varbinary(1000))
returns varchar(1000)
as
begin
DECLARE @Return varchar(1000), @ind int, @byte binary(1),@byte1 int, @byte2 int;
SELECT @Return = '',@ind = 1;
WHILE ( @ind <= datalength(@bin) )
BEGIN
SELECT @byte = substring(@bin, @ind, 1);
SET @byte1 = @byte / 16
IF(@byte1 >= 10)
SELECT @Return = @Return +
CASE @byte1
WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN 'D'
WHEN 14 THEN 'E'
WHEN 15 THEN 'F'
END
ELSE
SELECT @Return = @Return + convert(char(1),@byte1) SET @byte2 = @byte % 16
IF(@byte2 >= 10)
SELECT @Return = @Return +
CASE @byte2
WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN 'D'
WHEN 14 THEN 'E'
WHEN 15 THEN 'F'
END
ELSE
SELECT @Return = @Return + convert(char(1),@byte2) SELECT @ind = @ind + 1;
END RETURN @Return;
end--2.使用函数ConvertVarBinary_HexStr转换binary列值
select id,dbo.ConvertVarBinary_HexStr(bcol) 'bcol' from fy--结果(已是varchar类型).
id bcol
----------- ------------
1 26009DAD1F
2 0000000004(2 row(s) affected)