create function i2h (@i int)
returns char(3)
as
begin
declare @h int
declare @l int
declare @s char
declare @r varchar(3)
if @i >= 4096
return null
set @h = @i / 16
set @l = @i % 16
if @l < 10
set @s = cast(@l as char(1))
else
set @s = char(@l + 55)
if @h > 0
set @r = rtrim(dbo.i2h(@h))+@s
else
set @r = @s
return @r
endupdate yourtable set userid = dbo.i2h(id)
returns char(3)
as
begin
declare @h int
declare @l int
declare @s char
declare @r varchar(3)
if @i >= 4096
return null
set @h = @i / 16
set @l = @i % 16
if @l < 10
set @s = cast(@l as char(1))
else
set @s = char(@l + 55)
if @h > 0
set @r = rtrim(dbo.i2h(@h))+@s
else
set @r = @s
return @r
endupdate yourtable set userid = dbo.i2h(id)
设计表的时候加一个计算字段(公式):cast(你的标识列 as varbinary(3))如:
select cast(1 as varbinary(3))
如果你要转换成字符:
select master.dbo.fn_varbintohexstr(cast(1 as varbinary(3))) 字符类型
Select cast(2147483647 as binary(10)) ttt
select master.dbo.fn_varbintohexsubstring(0,cast(2147483647 as binary(10)),1,10)
select dbo.i2h(2147483647)
--输出结果
------------------------------
0x0000000000007FFFFFFF
------------------------------
0000000000007fffffff
------------------------------
7FFFFFFF
------------------------------
全部正确
alter function i2h (@i bigint)
returns char(30)
as
begin
declare @h bigint
declare @l bigint
declare @s char
declare @r varchar(30)
/*
if @i >= 4096
return null
*/
set @h = @i / 16
set @l = @i % 16
if @l < 10
set @s = cast(@l as char(1))
else
set @s = char(@l + 55)
if @h > 0
set @r = rtrim(dbo.i2h(@h))+@s
else
set @r = @s
return @r
end