如何在sql server中将数字写入word函数 [英] how to write number to word function in sql server

问题描述

如何在 SQL Server 中编写一个函数来输出一个以字为单位的数字?

How would one write a function in SQL Server to output a number in words?

输入:1
输出:一

input: 1
output: one

输入:129
输出:一百二十九

input: 129
output: one hundred twenty-nine

推荐答案

这是 Andrew 对上述答案的轻微修改(全部归功于 Andrew)，但这将在不使用任何函数的情况下在 sql 查询中返回结果

This is slight modification of above answer by Andrew (all credits to Andrew) but this will return results in sql query without using any function

    WITH Sequence
AS (
    SELECT 0 seq
        ,'' word

    UNION ALL

    SELECT 1
        ,'One'

    UNION ALL

    SELECT 2
        ,'Two'

    UNION ALL

    SELECT 3
        ,'Three'

    UNION ALL

    SELECT 4
        ,'Four'

    UNION ALL

    SELECT 5
        ,'Five'

    UNION ALL

    SELECT 6
        ,'Six'

    UNION ALL

    SELECT 7
        ,'Seven'

    UNION ALL

    SELECT 8
        ,'Eight'

    UNION ALL

    SELECT 9
        ,'Nine'

    UNION ALL

    SELECT 10
        ,'Ten'

    UNION ALL

    SELECT 11
        ,'Eleven'

    UNION ALL

    SELECT 12
        ,'Twelve'

    UNION ALL

    SELECT 13
        ,'Thirteen'

    UNION ALL

    SELECT 14
        ,'Fourteen'

    UNION ALL

    SELECT 15
        ,'Fifteen'

    UNION ALL

    SELECT 16
        ,'Sixteen'

    UNION ALL

    SELECT 17
        ,'Seventeen'

    UNION ALL

    SELECT 18
        ,'Eighteen'

    UNION ALL

    SELECT 19
        ,'Nineteen'

    UNION ALL

    SELECT 20
        ,'Twenty'

    UNION ALL

    SELECT 30
        ,'Thirty'

    UNION ALL

    SELECT 40
        ,'Forty'

    UNION ALL

    SELECT 50
        ,'Fifty'

    UNION ALL

    SELECT 60
        ,'Sixty'

    UNION ALL

    SELECT 70
        ,'Seventy'

    UNION ALL

    SELECT 80
        ,'Eighty'

    UNION ALL

    SELECT 90
        ,'Ninty'
    )
    ,CTENumbers
AS (
    SELECT 0 AS num  --changezero to your starting number

    UNION ALL

    SELECT num + 1  
    FROM CTENumbers
    WHERE num + 1 <= 255 --change 255 to upper limit
    )
SELECT *
    ,CASE 
        WHEN num = 0
            THEN 'Zero'
        WHEN num < 20
            THEN (
                    SELECT word
                    FROM sequence
                    WHERE seq = num
                    )
        WHEN num < 100
            THEN (
                    SELECT TTens.word + ' ' + TUnits.word
                    FROM Sequence AS TUnits
                    CROSS JOIN Sequence AS TTens
                    WHERE TUnits.seq = (num % 100) % 10
                        AND TTens.seq = (num % 100) - (num % 100) % 10
                    )
        WHEN num < 1000
            THEN (
                    SELECT THundreds.word + ' Hundred and ' + TTens.word + ' ' + TUnits.word
                    FROM Sequence AS TUnits
                    CROSS JOIN Sequence AS TTens
                    CROSS JOIN Sequence AS THundreds
                    WHERE TUnits.seq = (num % 100) % 10
                        AND TTens.seq = (num % 100) - (num % 100) % 10
                        AND THundreds.seq = (num / 100)
                    )
        WHEN num = 1000
            THEN (
                    SELECT TThousand.word + ' Thousand'
                    FROM Sequence AS TThousand
                    WHERE TThousand.seq = (num / 1000)
                    )
        WHEN num < 10000
            THEN (
                    SELECT TThousand.word + ' Thousand ' + THundreds.word + ' Hundred and ' + TTens.word + ' ' + TUnits.word
                    FROM Sequence AS TUnits
                    CROSS JOIN Sequence AS TTens
                    CROSS JOIN Sequence AS THundreds
                    CROSS JOIN Sequence AS TThousand
                    WHERE TUnits.seq = (num % 100) % 10
                        AND TTens.seq = (num % 100) - (num % 100) % 10
                        AND THundreds.seq = (num / 100) - (num / 1000) * 10
                        AND TThousand.seq = (num / 1000)
                    )
        ELSE STR(num)
        END Number
FROM CTENumbers
OPTION (MAXRECURSION 10000)

这篇关于如何在sql server中将数字写入word函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！