gpt4 book ai didi

sql - 将数字转换为单词-第一,第二,第三等

转载 作者:行者123 更新时间:2023-12-01 15:40:47 26 4
gpt4 key购买 nike

有谁知道如何在SQL中采用整数变量并将其转换为以下形式的等价数字:第一,第二,第三,第四等?

谢谢

最佳答案

这似乎是一种更简单的方法,一种不错的递归算法

CREATE FUNCTION fnIntegerToWords(@Number as BIGINT) 
RETURNS VARCHAR(1024)
AS

BEGIN
DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))
DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))
INSERT @Below20 (Word) VALUES
( 'Zero'), ('One'),( 'Two' ), ( 'Three'),
( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),
( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),
( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),
( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),
('Eighteen' ), ( 'Nineteen' )

INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),
('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

declare @belowHundred as varchar(126)

if @Number > 99 begin
select @belowHundred = dbo.fnIntegerToWords( @Number % 100)
end

DECLARE @English varchar(1024) =

(

SELECT Case
WHEN @Number = 0 THEN ''

WHEN @Number BETWEEN 1 AND 19
THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

WHEN @Number BETWEEN 20 AND 99
THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' +
dbo.fnIntegerToWords( @Number % 10)

WHEN @Number BETWEEN 100 AND 999
THEN (dbo.fnIntegerToWords( @Number / 100)) +' Hundred '+
Case WHEN @belowHundred <> '' THEN 'and ' + @belowHundred else @belowHundred end

WHEN @Number BETWEEN 1000 AND 999999
THEN (dbo.fnIntegerToWords( @Number / 1000))+' Thousand '+
dbo.fnIntegerToWords( @Number % 1000)

WHEN @Number BETWEEN 1000000 AND 999999999
THEN (dbo.fnIntegerToWords( @Number / 1000000))+' Million '+
dbo.fnIntegerToWords( @Number % 1000000)

WHEN @Number BETWEEN 1000000000 AND 999999999999
THEN (dbo.fnIntegerToWords( @Number / 1000000000))+' Billion '+
dbo.fnIntegerToWords( @Number % 1000000000)

ELSE ' INVALID INPUT' END
)

SELECT @English = RTRIM(@English)

SELECT @English = RTRIM(LEFT(@English,len(@English)-1))
WHERE RIGHT(@English,1)='-'

RETURN (@English)

END

关于sql - 将数字转换为单词-第一,第二,第三等,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8432527/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com