char类型的十六进制数怎么转换成char类型的十进制数？

[kekan]

有一char类型列值均为十六进制，如何实现全部转换成十进制？

[$扫地僧$]

请参考:
http://topic.csdn.net/u/20080218/17/0f6c72a2-a214-42e8-ab80-7e2f1b712c48.html

[xieyueqing]

拼字符串

create table T(id int,a varchar(20),b int)
insert into T values (1,'0x2A',null);
insert into T values (2,'0x1A',null);
insert into T values (3,'0x10',null);
go
declare @SQL varchar(1000),@id int,@a varchar(20);

declare cur cursor for select id,a from T
open cur
fetch cur into @id,@a;
while @@fetch_status =0
begin
	set @SQL = 'update T set b= cast(' + @a + ' as int) where id =' + cast(@id as varchar(20));
	exec(@SQL);

	fetch cur into @id,@a;
end
close cur;
deallocate cur;

go
drop table T

[loworth]

[Quote=引用 1 楼 scmail81 的回复:]
请参考:
http://topic.csdn.net/u/20080218/17/0f6c72a2-a214-42e8-ab80-7e2f1b712c48.html
[/Quote]

确实很帅

CREATE FUNCTION dbo.f_hex_dec ( @s VARCHAR(16) )
RETURNS BIGINT
AS BEGIN
    DECLARE @i INT,
        @result BIGINT
    SELECT  @i = 0,
            @result = 0,
            @s = RTRIM(LTRIM(UPPER(REVERSE(@s))))
    WHILE @i < LEN(@s)
        BEGIN
            IF SUBSTRING(@s, @i + 1, 1) NOT BETWEEN '0' AND '9'
                AND SUBSTRING(@s, @i + 1, 1) NOT BETWEEN 'A' AND 'F' 
                BEGIN
                    SELECT  @result = 0
                    BREAK
                END
            SELECT  @result = @result + ( CHARINDEX(SUBSTRING(@s, @i + 1, 1),
                                                    '0123456789ABCDEF') - 1 )
                    * POWER(16, @i),
                    @i = @i + 1
        END
    RETURN @result
   END
GO


--测试


SELECT dbo.f_hex_dec('FFF')