sql-server - 如何在sql server中将数字写入word函数

Question

关闭。这个问题需要多问 focused 。目前不接受答案。

---

想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post .

已关闭 5 年前。

Improve this question

如何在 SQL Server 中编写一个函数来以文字形式输出数字？

输入:1
输出:一个

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

Answer 1

考虑使用辅助数字表。

注意:这个 MS SQL

创建序列表 - 这可以包含您需要的所有数字或至少最多 999 个数字。我已将其限制为最少，但它增加了额外的逻辑。

CREATE TABLE [dbo].[Sequence]
    (
      seq INTEGER NOT NULL UNIQUE,
      word [varchar](25) NOT NULL
    )

INSERT INTO [Sequence] SELECT 0, ''
INSERT INTO [Sequence] SELECT 1, 'One'
INSERT INTO [Sequence] SELECT 2, 'Two'
INSERT INTO [Sequence] SELECT 3, 'Three'
INSERT INTO [Sequence] SELECT 4, 'Four'
INSERT INTO [Sequence] SELECT 5, 'Five'
INSERT INTO [Sequence] SELECT 6, 'Six'
INSERT INTO [Sequence] SELECT 7, 'Seven'
INSERT INTO [Sequence] SELECT 8, 'Eight'
INSERT INTO [Sequence] SELECT 9, 'Nine'

INSERT INTO [Sequence] SELECT 10, 'Ten'
INSERT INTO [Sequence] SELECT 11, 'Eleven'
INSERT INTO [Sequence] SELECT 12, 'Twelve'
INSERT INTO [Sequence] SELECT 13, 'Thirteen'
INSERT INTO [Sequence] SELECT 14, 'Fourteen'
INSERT INTO [Sequence] SELECT 15, 'Fifteen'
INSERT INTO [Sequence] SELECT 16, 'Sixteen'
INSERT INTO [Sequence] SELECT 17, 'Seventeen'
INSERT INTO [Sequence] SELECT 18, 'Eighteen'
INSERT INTO [Sequence] SELECT 19, 'Nineteen'

INSERT INTO [Sequence] SELECT 20, 'Twenty'
INSERT INTO [Sequence] SELECT 30, 'Thirty'
INSERT INTO [Sequence] SELECT 40, 'Forty'
INSERT INTO [Sequence] SELECT 50, 'Fifty'
INSERT INTO [Sequence] SELECT 60, 'Sixty'
INSERT INTO [Sequence] SELECT 70, 'Seventy'
INSERT INTO [Sequence] SELECT 80, 'Eighty'
INSERT INTO [Sequence] SELECT 90, 'Ninty'

然后创建用户定义的函数。

CREATE FUNCTION dbo.udf_NumToWords ( 
                @num AS INTEGER 
)       RETURNS VARCHAR(50)
AS
BEGIN

DECLARE @words AS VARCHAR(50)

IF      @num =     0 SELECT @words = 'Zero'
ELSE IF @num <    20 SELECT @words = word FROM sequence WHERE seq = @num
ELSE IF @num <   100 (SELECT @words = 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 
                    )
ELSE IF @num =   100 (SELECT @words = THundreds.word + ' Hundred'
                      FROM Sequence AS THundreds
                     WHERE THundreds.seq = (@num / 100)
                    )
ELSE IF @num <  1000 (
                    SELECT @words = 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)
                    )
ELSE IF @num =  1000 (SELECT @words = TThousand.word + ' Thousand'
                      FROM Sequence AS TThousand
                     WHERE TThousand.seq = (@num / 1000)
                    )
ELSE IF @num < 10000 (
                    SELECT @words = 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 SELECT @words = STR(@num)

RETURN @words

END

现在测试功能:

SELECT NumberAsWords = dbo.udf_NumToWords(888);