好像没有系统函数,得写个函数才行:--==============================================
-- FUNCTION varbin2hexstr
-- 将 varbinary 类型的数据转换为 varchar 类型
--==============================================
IF OBJECT_ID ('dbo.varbin2hexstr') IS NOT NULL
DROP FUNCTION dbo.varbin2hexstr
GO
CREATE function varbin2hexstr(
@bin varbinary(8000)
)returns varchar(8000)
as
begin
declare @re varchar(8000),@i int
select @re='',@i=datalength(@bin)
while @i>0
select @re=substring('0123456789ABCDEF',substring(@bin,@i,1)/16+1,1)
+substring('0123456789ABCDEF',substring(@bin,@i,1)%16+1,1)
+@re
,@i=@i-1
-- return('0x'+@re)
return @re
end
GO
select right(dbo.varbin2hexstr(0xBEC4),2)+
left(dbo.varbin2hexstr(0xBEC4),2)
/*
C4BE
*/
-- FUNCTION varbin2hexstr
-- 将 varbinary 类型的数据转换为 varchar 类型
--==============================================
IF OBJECT_ID ('dbo.varbin2hexstr') IS NOT NULL
DROP FUNCTION dbo.varbin2hexstr
GO
CREATE function varbin2hexstr(
@bin varbinary(8000)
)returns varchar(8000)
as
begin
declare @re varchar(8000),@i int
select @re='',@i=datalength(@bin)
while @i>0
select @re=substring('0123456789ABCDEF',substring(@bin,@i,1)/16+1,1)
+substring('0123456789ABCDEF',substring(@bin,@i,1)%16+1,1)
+@re
,@i=@i-1
-- return('0x'+@re)
return @re
end
GO
select right(dbo.varbin2hexstr(0xBEC4),2)+
left(dbo.varbin2hexstr(0xBEC4),2)
/*
C4BE
*/
DECLARE @b1 INT,@c1 INT set @a=0xBEC4
SELECT @b1= (CONVERT(INT,@a) & 0xFF00)/256
SELECT @c1= (CONVERT(INT,@a) & 0x00FF)*256
SELECT CONVERT(VARBINARY(2), @b1 |@c1)
--result
/*
0xC4BE
*/SELECT UNICODE('木')
SET @J = 48836;SET @K = 13678;
SELECT CONVERT(VARBINARY(2),(@J%256*256)+(@J/256)), CONVERT(VARBINARY(2),(@K%256*256)+(@K/256))
--result
/*
0xC4BE, 0x6E35
*/
DECLARE @J INT, @K INT;
SET @J = 48836;SET @K = 13678;
SELECT CONVERT(VARBINARY(2),(@J*256+@J/256)), CONVERT(VARBINARY(2),(@K*256+@K/256))
--result
/*
0xC4BE, 0x6E35
*/
请问LZ的0xC4BE是怎么算出来的?select convert(varbinary(2),unicode('木')) 'h'
/*
h
------
0x6728(1 row(s) affected)
*/
select nchar(convert(int,0x6728)) 'z'
/*
z
----
木(1 row(s) affected)
*/