中文阿拉伯数据大小写转换(整理)

IF OBJECT_ID('[dbo].[f_digit_chn]')>0

    DROP FUNCTION [dbo].[f_digit_chn]

GO

CREATE FUNCTION [dbo].[f_digit_chn]

(

    @num BIGINT

)

RETURNS VARCHAR(20)

AS

BEGIN

    DECLARE @result VARCHAR(20), @symbol VARCHAR(2)

    IF @num<0

        SELECT @symbol = '负', @result = '', @num = ABS(@num)

    ELSE

       SELECT @symbol = '', @result = ''

    WHILE @num<>0

          SELECT @result = SUBSTRING('零一二三四五六七八九十', @num%10+1, 1)+@result, @num = @num/10

    RETURN @symbol+@result

END

GO

SELECT dbo.[f_digit_chn](2010)

/*

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

二零一零

 

(1 行受影响)

*/

IF OBJECT_ID('fun_cgnum')>0

    DROP FUNCTION fun_cgnum

GO

CREATE FUNCTION fun_cgnum

(

    @num INT

)

RETURNS VARCHAR(100)

AS

BEGIN

    DECLARE @temp INT, @res INT, @i TINYINT

    DECLARE @str VARCHAR(100), @no VARCHAR(20), @unit  VARCHAR(16)

    SELECT @str = '', @no = '另壹贰叁肆伍陆柒捌玖', @unit = '拾佰仟万拾佰仟亿'

    SET @temp = @num

    SELECT @i = 0, @res = @temp%10, @temp = @temp/10

    WHILE @temp>0

    BEGIN

        IF @i=0

            SET @str = SUBSTRING(@no, @res+1, 1)

        ELSE

        SET @str = SUBSTRING(@no, @res+1, 1)+SUBSTRING(@unit, @i, 1)+@str

        SELECT @res = @temp%10, @temp = @temp/10

        SET @i = @i+1

    END

    SET @str = SUBSTRING(@no, @res+1, 1)+SUBSTRING(@unit, @i, 1)+@str

    SET @str = REPLACE(@str, '另拾', '另')

    SET @str = REPLACE(@str, '另佰', '另')

    SET @str = REPLACE(@str, '另仟', '另')

    SET @str = REPLACE(@str, '另拾', '另')

    SET @str = REPLACE(@str, '另万', '万')

    WHILE @i>0

    BEGIN

        SET @str = REPLACE(@str, '另另', '另')

        SET @i = CHARINDEX('另另', @str)

    END

    SET @str = REPLACE(@str, '另万', '万')

    SET @str = REPLACE(@str, '亿万', '亿')

    IF RIGHT(@str, 1)='另'

        SET @str = LEFT(@str, LEN(@str)-1)

    RETURN @str

END

GO

 

--测试结果

SELECT dbo.fun_cgnum(123456789),dbo.fun_cgnum(9089000),dbo.fun_cgnum(20)

/*

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

壹亿贰仟叁佰肆拾伍万          玖佰另捌万玖仟             贰拾

 

(1 行受影响)

*/