SQL Server进制转换函数的创建

十进制转换为二进制、八进制、或十六进制:

CREATE FUNCTION [dbo].[Scalar_IntConvertToAny]  
(  
    @NUM INT,  
    @TYPE INT  
)  
RETURNS VARCHAR(100)  
AS  
BEGIN  
    DECLARE @RESULT VARCHAR(500)='';   
    WITH CTE AS(   
    SELECT @NUM/@TYPE D,@NUM%@TYPE S,1 [INDEX]   
    UNION ALL   
    SELECT D/@TYPE , D%@TYPE,[INDEX]+1 FROM CTE WHERE D>0   
    )   
    SELECT @RESULT+=CASE CAST(S AS VARCHAR(5)) WHEN '10' THEN 'A'   
            WHEN '11' THEN 'B'   
            WHEN '12' THEN 'C'   
            WHEN '13' THEN 'D'   
            WHEN '14' THEN 'E'   
            WHEN '15' THEN 'F'   
            ELSE CAST(S AS VARCHAR(5)) END   
    FROM CTE ORDER BY [INDEX] DESC   
    RETURN @RESULT  
END  

示例：将整数216分别转换为二进制和十六进制

二进制、八进制、或十六进制转换为十进制：

CREATE FUNCTION [dbo].[Scalar_AnyConvertToInt]
(	
	@INPUT VARCHAR(MAX),
	@TYPE INT
)
RETURNS int
AS
BEGIN
	DECLARE @SUM INT;
	WITH CTE AS(
	SELECT CAST(CASE SUBSTRING(@INPUT,1,1) WHEN 'A' THEN '10'
			WHEN 'B' THEN '11'
			WHEN 'C' THEN '12'
			WHEN 'D' THEN '13'
			WHEN 'E' THEN '14'
			WHEN 'F' THEN '15'
			ELSE SUBSTRING(@INPUT,1,1) END
			AS INT)*POWER(@TYPE,LEN(@INPUT)-1)NUM,1 [INDEX]
	UNION ALL
	SELECT CAST(CASE SUBSTRING(@INPUT,[INDEX]+1,1) WHEN 'A' THEN '10'
    		WHEN 'B' THEN '11'
    		WHEN 'C' THEN '12'
    		WHEN 'D' THEN '13'
    		WHEN 'E' THEN '14'
    		WHEN 'F' THEN '15'
    		ELSE SUBSTRING(@INPUT,[INDEX]+1,1) END
    		AS INT)*POWER(@TYPE,LEN(@INPUT)-[INDEX]-1),[INDEX]+1 NUM
	FROM CTE WHERE [INDEX]<LEN(@INPUT)
	)SELECT @SUM=SUM(NUM) FROM CTE	
	RETURN @SUM

END

示例：将分别将二进制和16进制数转换为十进制整数

除了二进制、八进制、十六进制和十进制之间的互相转换，二进制、八进制、十六进制之间也可以互相转换，下面我就举个二进制转换为八进制的列子吧！