亂馬客

CREATE  Function dbo.fn_NumToCNum(@intValue int)

returns varchar(40)
as
BEGIN
    DECLARE @strNum char(40)
    DECLARE @strNum1 char(20)
    DECLARE @strUnin char(20)
    DECLARE @strNumNo char(20)
    DECLARE @strValue char(20)
    DECLARE @strString char(40)
    DECLARE @intStep int
    DECLARE @intLen int
    DECLARE @intCount int

    IF @intValue>10 AND @intValue<20
    BEGIN
        SELECT @strNum='十'
        SELECT @strNum1= CONVERT(CHAR,(@intValue%10))
        SELECT @strNum1= CASE WHEN @strNum1='1' THEN '一'
                         WHEN @strNum1='2' THEN '二'
                         WHEN @strNum1='3' THEN '三'
                         WHEN @strNum1='4' THEN '四'
                         WHEN @strNum1='5' THEN '五'
                         WHEN @strNum1='6' THEN '六'
                         WHEN @strNum1='7' THEN '七'
                         WHEN @strNum1='8' THEN '八'
                         WHEN @strNum1='9' THEN '九'
                         WHEN @strNum1='0' THEN '零'

                        END
        SET @strNum=RTRIM(@strNum)+RTRIM(@strNum1)
    END

    IF @intValue=10
    BEGIN
        SELECT @strNum='十'
    END

    IF @intValue<10 or @intValue>=20
    BEGIN

        SET @strNum=RTRIM(CONVERT(CHAR(20),@intValue))
        SET @intLen=LEN(@strNum)
        SET @strString=''
        SET @strNumNo=''
        SET @intStep=1
        SET @intCount=0

        WHILE @intStep<= @intLen
            BEGIN

            SET @strValue=SUBSTRING(@strNum,@intLen+1-@intStep,1)    
                SET @strUnin=''
                SET @strNumNo=''
                    BEGIN
                        SELECT @strNumNo=RTRIM(@strValue)
                        SELECT @strNumNo= CASE WHEN @strNumNo='1' THEN '一'
                                                 WHEN @strNumNo='2' THEN '二'
                                                WHEN @strNumNo='3' THEN '三'
                                                 WHEN @strNumNo='4' THEN '四'
                                                WHEN @strNumNo='5' THEN '五'
                                                 WHEN @strNumNo='6' THEN '六'
                                                 WHEN @strNumNo='7' THEN '七'
                                                WHEN @strNumNo='8' THEN '八'
                                               WHEN @strNumNo='9' THEN '九'
                                                 WHEN @strNumNo='0' THEN '零'

                                                            END

                        SET @intCount = @intCount + 1
                        SET @intStep = @intStep + 1
                        IF @intCount=2 OR @intCount=6
                                 SET @strUnin = '十'
                                IF @intCount=3 OR @intCount=7
                                 SET @strUnin = '百'
                                IF @intCount=4 OR @intCount=8
                                SET @strUnin = '千'
                                IF @intCount=5
                                SET @strUnin = '萬'
                                IF @intCount=9
                            SET @strUnin = '億'
                        IF @strNumNo=''
                            SET @strUnin = ''
                    END
                SET @strString=RTRIM(@strNumNo)+RTRIM(@strUnin)+RTRIM(@strString)
            END
        SET @strNum=@strString

        --若最後一位數為 0，則將該數字去掉
        IF RIGHT(@intValue,1)='0'
            SET @strNum=LEFT(@strNum,LEN(rtrim(@strNum))-1)
        IF @intValue=100
            SET @strNum=REPLACE(@strNum,'零十','')
        IF @intValue<110 AND @intValue>101
            SET @strNum=REPLACE(@strNum,'零十','零')

    END

    RETURN rtrim(@strNum)
END

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^