SQL實作16進制到10進制的轉換

SQL 實作對 16進制 到10進制的轉換

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 @[email protected] +(CHARINDEX( SUBSTRING(@s ,@i+ 1,1 ),'0123456789ABCDEF')- 1)*POWER (16, @i),@i [email protected]+ 1
  END
  RETURN @result
END
GO

select distinct (dbo. f_hex_dec(substring (ExETCTermCode, 8,5 ))) as ExETCTermCode , ExRoadID,ExStationID ,ExLaneID from tb_LaneExList201406 where ExETCTermCode <>'0' order by ExRoadID ,ExStationID, ExLaneID
           

函數解釋： RTRIM 、LTRIM 去除字元串中的左右空白； REVERSE 翻轉字元串；CHARINDEX（S1,S2,index）尋找并傳回S1在S2中的位置，index 為開始尋找位置，不填，負數或0均預設從S2開頭位置尋找