mysql十进制转二进制函数,sql函数之十六进制与十进制间的转换

一、十进制转换成十六进制

第一种情况：待转换数字随机

CREATE FUNCTION ufn_ConvertInt2Hex ( @Num BIGINT )

RETURNS VARCHAR(500)

AS

BEGIN

DECLARE @Result VARCHAR(500)

SET @Result = ''

WHILE ( @Num > 0 )

BEGIN

SET @Result = SUBSTRING('0123456789ABCDEF', @Num % 16 + 1, 1)

+ @Result

SET @Num = @Num / 16

END

RETURN @Result

END

GO

第二种情况：假如已知待转数字对应十六进制数都是两位数

SELECT

CASE cloumn_name / 16 % 10 --计算转换为十六进制后十位上的数字

WHEN '0' THEN 'A'

WHEN '1' THEN 'B'

WHEN '2' THEN 'C'

WHEN '3' THEN 'D'

WHEN '4' THEN 'E'

ELSE 'F'

END +

CASE cloumn_name % 16  --计算个位上的数字

WHEN '10' THEN 'A' --cast函数：实现数据类型转换

WHEN '11' THEN 'B'

WHEN '12' THEN 'C'

WHEN '13' THEN 'D'

WHEN '14' THEN 'E'

ELSE CAST (cloumn_name % 16 AS VARCHAR (50)) --int类型转换为varchar

END

FROM table_name

二、十六进制转换成十进制

CREATE FUNCTION ufn_ConvertHex2Int ( @HexString VARCHAR(16) )

RETURNS BIGINT

AS

BEGIN

DECLARE @Result BIGINT

DECLARE @i INT ,

@len INT

DECLARE @power BIGINT

SET @power = 16

SELECT @i = 0 ,

@Result = 0 ,

@HexString = RTRIM(LTRIM(UPPER(@HexString)))

SET @len = LEN(@HexString)

IF ( @len = 16 )

BEGIN

IF ( ASCII(SUBSTRING(@HexString, 1, 1)) > 55 )

BEGIN

-- RaisError('超出数据运算范围', 1, 16)

RETURN @Result

END

END

-------------------------------------------------------

WHILE ( @i < @len )

BEGIN

IF ( ( SUBSTRING(@HexString, @len - @i, 1) NOT BETWEEN '0' AND '9' )

AND ( SUBSTRING(@HexString, @len - @i, 1) NOT BETWEEN 'A' AND 'F' )

)

BEGIN

SET @Result = 0

BREAK;

END

----------------------------------------

SET @Result = @Result + ( CHARINDEX(SUBSTRING(@HexString,

@len - @i, 1),

'0123456789ABCDEF') - 1 )

* CAST(POWER(@power, @i) AS BIGINT)

SET @i = @i + 1

END

----------------------------------------------

RETURN @Result

END