有人知道如何在SQL中将整数变量转换为相应形式的数字：第一、第二、第三、第四等吗？
谢谢
将数字转换为单词-第一，第二，第三等。
9
- Danny
6
我应该说，这是 MS SQL。 - Danny
什么版本？这可能是相关的。 - JNK
你的数字可能有多大？ - Dibstar
如果您的数字不超过999，我提供了一个可能有用的解决方案，尽管它并不是最美观的！ - Dibstar
@JNK 感谢您的跟进，我直到周五晚上才看到这个消息，所以我将在周一早上回复您。 - Danny
@Davin 就像之前说的一样，感谢您的跟进，我将在周一进行测试。 - Danny
6个回答
11
这似乎是一种更简单的方法，一个不错的递归算法
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
- Matthew Hintzen
1
这对我所需的工作效果很好。我用它来加载查找表。这是一个不错的递归函数，可以保持逻辑简单。 - Nick Harrison
4
您需要使用一个
CASE 语句：SELECT intfield CASE WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
...other conversions...
ELSE '' END
FROM Mytable
编辑:
以下是针对三位数字的工作代码。您可以在其下方添加所需的代码，以处理两位和一位数字（基本上只需修改我编写的内容并相应更改SUBSTRING）。
它将数字转换为字符串，检查其中有多少个数字，然后相应地进行拆分。
DECLARE @n int = 515
DECLARE @v varchar(100) = CAST(@N as varchar)
SELECT CASE WHEN LEN(@v) = 3 THEN
(CASE WHEN LEFT(@v, 1) = 1 THEN 'One Hundred'
WHEN LEFT(@v, 1) = 2 THEN 'Two Hundred'
WHEN LEFT(@v, 1) = 3 THEN 'Three Hundred'
WHEN LEFT(@v, 1) = 4 THEN 'Four Hundred'
WHEN LEFT(@v, 1) = 5 THEN 'Five Hundred'
WHEN LEFT(@v, 1) = 6 THEN 'Six Hundred'
WHEN LEFT(@v, 1) = 7 THEN 'Seven Hundred'
WHEN LEFT(@v, 1) = 8 THEN 'Eight Hundred'
WHEN LEFT(@v, 1) = 9 THEN 'Nine Hundred'
ELSE '' END)
+ ' ' +
(CASE WHEN SUBSTRING(@v, 2, 1) = 2 THEN 'Twenty'
WHEN SUBSTRING(@v, 2, 1) = 3 THEN 'Thirty'
WHEN SUBSTRING(@v, 2, 1) = 4 THEN 'Forty'
WHEN SUBSTRING(@v, 2, 1) = 5 THEN 'Fifty'
WHEN SUBSTRING(@v, 2, 1) = 6 THEN 'Sixty'
WHEN SUBSTRING(@v, 2, 1) = 7 THEN 'Seventy'
WHEN SUBSTRING(@v, 2, 1) = 8 THEN 'Eighty'
WHEN SUBSTRING(@v, 2, 1) = 9 THEN 'Ninety'
WHEN SUBSTRING(@v, 2, 1) = 1 THEN
CASE WHEN SUBSTRING(@v, 2, 2) = 10 THEN 'Ten'
WHEN SUBSTRING(@v, 2, 2) = 11 THEN 'Eleven'
WHEN SUBSTRING(@v, 2, 2) = 12 THEN 'Twelve'
WHEN SUBSTRING(@v, 2, 2) = 13 THEN 'Thirteen'
WHEN SUBSTRING(@v, 2, 2) = 14 THEN 'Fourteen'
WHEN SUBSTRING(@v, 2, 2) = 15 THEN 'Fifteen'
WHEN SUBSTRING(@v, 2, 2) = 16 THEN 'Sixteen'
WHEN SUBSTRING(@v, 2, 2) = 17 THEN 'Seventeen'
WHEN SUBSTRING(@v, 2, 2) = 18 THEN 'Eighteen'
WHEN SUBSTRING(@v, 2, 2) = 19 THEN 'Nineteen'
ELSE '' END
ELSE '' END)
+ ' ' +
(CASE WHEN SUBSTRING(@v, 2, 1) = 1 THEN ''
WHEN SUBSTRING(@v, 3, 1) = 2 THEN 'Two'
WHEN SUBSTRING(@v, 3, 1) = 3 THEN 'Three'
WHEN SUBSTRING(@v, 3, 1) = 4 THEN 'Four'
WHEN SUBSTRING(@v, 3, 1) = 5 THEN 'Five'
WHEN SUBSTRING(@v, 3, 1) = 6 THEN 'Six'
WHEN SUBSTRING(@v, 3, 1) = 7 THEN 'Seven'
WHEN SUBSTRING(@v, 3, 1) = 8 THEN 'Eight'
WHEN SUBSTRING(@v, 3, 1) = 9 THEN 'Nine'
WHEN SUBSTRING(@v, 3, 1) = 1 THEN 'One'
ELSE '' END)
END
- JNK
6
1那么你不应该尝试在SQL中实现它。 - JNK
我很理解这种事情不应该在SQL环境中完成，但不幸的是我们目前没有其他选择。 - Danny
@Beanz - 在SQL中，这是正确的方法。在应用程序层面上，可能有一个可以用于此的API。如果您只能使用TSQL，则
CASE是最好的选择。您还可以为此制作一个表，并对其进行JOIN。 - JNK+1 这真的是你唯一的选择。一旦你接触到像“twenty-first”、“thirty-second”等这样的东西，你就能注意到英语语言中的一些模式。 - Yuck
@Beanz - 发布一些可用的代码。您可以根据需要进行修改。 - JNK
@Yuck - 我在编辑中进行了扩展，实际上考虑到1000以下的所有数字并不是非常复杂。 - JNK
1
此外，您可以使用数字及其名称来CREATE TABLE。
CREATE TABLE tblNumbers (Nmb int, NmbWord varchar(100))
INSERT INTO tblNumbers
VALUES (1,'first'), (2,'second'),(3,'third'), (4,'forth')
CREATE TABLE SomeTable (nmb int)
INSERT INTO SomeTable
VALUES (1), (1),(2),(3)
SELECT N.*
FROM tblNumbers N
JOIN SomeTable ST ON ST.nmb=N.Nmb
DROP TABLE tblNumbers
DROP TABLE SomeTable
- Igor Borisenko
1
如果需要增加这个功能，那肯定是有可能的。谢谢。 - Danny
0
试试这个:
WITH [cte] AS (
SELECT [id]
,CONVERT([bigint], [number]) AS [number]
FROM (VALUES (1, 987654321)
,(2, 123456789)) AS [T]([id], [number]))
,[cte_i] AS (
SELECT [id], [number], [number] % POWER(10, 1) / POWER(10, 0) AS [digit], (1) AS [position]
FROM [cte]
UNION ALL
SELECT [id], [number], [number] % POWER(CAST((10) AS [bigint]), [position] + 1) / POWER(CAST((10) AS [bigint]), [position]), [position] + 1
FROM [cte_i]
WHERE LEN([number]) != [position])
,[cte_ii] AS (
SELECT [id]
,[number]
,[digit]
,[position]
,LEAD([digit]) OVER (PARTITION BY [id] ORDER BY [position]) AS [digit_lead]
,LAG([digit]) OVER (PARTITION BY [id] ORDER BY [position]) AS [digit_lag]
FROM [cte_i])
,[cte_iii] AS (
SELECT [id]
,[position]
,CASE
WHEN [digit] != 0 AND [position] % 3 = 1 AND ([digit_lead] != 1 OR [digit_lead] IS NULL) THEN
CASE [digit]
WHEN 1 THEN 'One'
WHEN 2 THEN 'Two'
WHEN 3 THEN 'Three'
WHEN 4 THEN 'Four'
WHEN 5 THEN 'Five'
WHEN 6 THEN 'Six'
WHEN 7 THEN 'Seven'
WHEN 8 THEN 'Eight'
WHEN 9 THEN 'Nine'
END
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 1 AND [digit_lag] = 0 THEN 'Ten'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 1 AND [digit_lag] = 1 THEN 'Eleven'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 1 AND [digit_lag] = 2 THEN 'Twelve'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 1 AND [digit_lag] = 3 THEN 'Thirteen'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 1 AND [digit_lag] = 4 THEN 'Fourteen'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 1 AND [digit_lag] = 5 THEN 'Fifteen'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 1 AND [digit_lag] = 6 THEN 'Sixteen'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 1 AND [digit_lag] = 7 THEN 'Seventeen'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 1 AND [digit_lag] = 8 THEN 'Eighteen'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 1 AND [digit_lag] = 9 THEN 'Nineteen'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 2 THEN 'Twenty'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 3 THEN 'Thirty'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 4 THEN 'Forty'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 5 THEN 'Fifty'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 6 THEN 'Sixty'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 7 THEN 'Seventy'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 8 THEN 'Eighty'
WHEN [digit] != 0 AND [position] % 3 = 2 AND [digit] = 9 THEN 'Ninety'
WHEN [digit] != 0 AND [position] % 3 = 0 AND [digit] = 1 THEN 'One Hundred'
WHEN [digit] != 0 AND [position] % 3 = 0 AND [digit] = 2 THEN 'Two Hundred'
WHEN [digit] != 0 AND [position] % 3 = 0 AND [digit] = 3 THEN 'Three Hundred'
WHEN [digit] != 0 AND [position] % 3 = 0 AND [digit] = 4 THEN 'Four Hundred'
WHEN [digit] != 0 AND [position] % 3 = 0 AND [digit] = 5 THEN 'Five Hundred'
WHEN [digit] != 0 AND [position] % 3 = 0 AND [digit] = 6 THEN 'Six Hundred'
WHEN [digit] != 0 AND [position] % 3 = 0 AND [digit] = 7 THEN 'Seven Hundred'
WHEN [digit] != 0 AND [position] % 3 = 0 AND [digit] = 8 THEN 'Eight Hundred'
WHEN [digit] != 0 AND [position] % 3 = 0 AND [digit] = 9 THEN 'Nine Hundred'
END AS [digit]
,CASE
WHEN [digit] != 0 AND [position] / 4 = 1 AND [position] % 4 IN (0, 1, 2) THEN 'Thousand'
WHEN [digit] != 0 AND [position] / 7 = 1 AND [position] % 7 IN (0, 1, 2) THEN 'Million'
WHEN [digit] != 0 AND [position] / 10 = 1 AND [position] % 10 IN (0, 1, 2) THEN 'Billion'
WHEN [digit] != 0 AND [position] / 13 = 1 AND [position] % 13 IN (0, 1, 2) THEN 'Trillion'
WHEN [digit] != 0 AND [position] / 16 = 1 AND [position] % 16 IN (0, 1, 2) THEN 'Quadrillion'
WHEN [digit] != 0 AND [position] / 19 = 1 AND [position] % 19 IN (0, 1, 2) THEN 'Sextillion'
END AS [group]
FROM [cte_ii])
,[cte_iv] AS (
SELECT [id]
,[group]
,MIN([position]) AS [position]
,STRING_AGG([digit], ' ') WITHIN GROUP (ORDER BY [position] DESC) AS [digit]
FROM [cte_iii]
GROUP BY [id]
,[group])
,[cte_v] AS (
SELECT [id]
,[position]
,IIF([digit] IS NOT NULL, CONCAT([digit], IIF([group] IS NOT NULL, ' ', NULL), [group]), NULL) AS [name]
FROM [cte_iv])
,[cte_vi] AS (
SELECT [id]
,STRING_AGG([name], ', ') WITHIN GROUP (ORDER BY [position] DESC) AS [name]
FROM [cte_v]
WHERE [name] != ''
GROUP BY [id])
SELECT [id]
,[name]
FROM [cte_vi]
- Farzad
0
这应该可以工作到999...
IF OBJECT_ID('tempdb..#numbers_list') IS NOT NULL DROP TABLE #numbers_list
CREATE TABLE #numbers_list (int_value INT, text_value VARCHAR(100))
INSERT INTO #numbers_list
SELECT 1 as int_value, 'One' as text_value UNION ALL
SELECT 2 as int_value, 'Two' as text_value UNION ALL
SELECT 3 as int_value, 'Three' as text_value UNION ALL
SELECT 4 as int_value, 'Four' as text_value UNION ALL
SELECT 5 as int_value, 'Five' as text_value UNION ALL
SELECT 6 as int_value, 'Six' as text_value UNION ALL
SELECT 7 as int_value, 'Seven' as text_value UNION ALL
SELECT 8 as int_value, 'Eight' as text_value UNION ALL
SELECT 9 as int_value, 'Nine' as text_value UNION ALL
SELECT 10 as int_value, 'Ten' as text_value UNION ALL
SELECT 11 as int_value, 'Eleven' as text_value UNION ALL
SELECT 12 as int_value, 'Twelve' as text_value UNION ALL
SELECT 13 as int_value, 'Thirteen' as text_value UNION ALL
SELECT 14 as int_value, 'Fourteen' as text_value UNION ALL
SELECT 15 as int_value, 'Fifteen' as text_value UNION ALL
SELECT 16 as int_value, 'Sixteen' as text_value UNION ALL
SELECT 17 as int_value, 'Seventeen' as text_value UNION ALL
SELECT 18 as int_value, 'Eighteen' as text_value UNION ALL
SELECT 19 as int_value, 'Nineteen' as text_value UNION ALL
SELECT 20 as int_value, 'Twenty' as text_value UNION ALL
SELECT 30 as int_value, 'Thirty' as text_value UNION ALL
SELECT 40 as int_value, 'Fourty' as text_value UNION ALL
SELECT 50 as int_value, 'Fifty' as text_value UNION ALL
SELECT 60 as int_value, 'Sixty' as text_value UNION ALL
SELECT 70 as int_value, 'Seventy' as text_value UNION ALL
SELECT 80 as int_value, 'Eighty' as text_value UNION ALL
SELECT 90 as int_value, 'Ninety' as text_value UNION ALL
SELECT 100 AS int_value, 'One Hundred' AS text_value
SELECT
tst2.number
,CASE WHEN exact.text_value IS NOT NULL THEN exact.text_value
WHEN tst2.number > 100 AND COALESCE(tens.text_value,ones.text_value) IS NULL THEN hunds.text_value + ' Hundred'
WHEN tst2.number > 100 AND tst2.number_of_ones = 0 THEN hunds.text_value + ' Hundred And ' + tens.text_value
WHEN tst2.number > 100 AND tst2.number_of_tens = 0 THEN hunds.text_value + ' Hundred And ' + ones.text_value
WHEN tst2.number > 100 THEN hunds.text_value + ' Hundred And ' + tens.text_value + ' ' + ones.text_value
WHEN tst2.number > 10 THEN tens.text_value + ' ' + ones.text_value
END AS string
FROM
(
SELECT tst.number
,tst.number % 10 as number_of_ones
,CASE WHEN tst.number < 101 THEN tst.number - (tst.number % 10)
WHEN tst.number > 100 THEN tst.number - 100 * (FLOOR(tst.number / 100)) - (tst.number % 10)
END as number_of_tens
,tst.number / 100 as number_of_hundreds
FROM
(
SELECT 22 as number UNION ALL
SELECT 27 UNION ALL
SELECT 10 UNION ALL
SELECT 101 UNION ALL
SELECT 200 UNION ALL
SELECT 227
) tst
) tst2
LEFT OUTER JOIN #numbers_list exact on exact.int_value = tst2.number --if an exact match
LEFT OUTER JOIN #numbers_list ones on ones.int_value = tst2.number_of_ones
LEFT OUTER JOIN #numbers_list tens on tens.int_value = tst2.number_of_tens
LEFT OUTER JOIN #numbers_list hunds on hunds.int_value = tst2.number_of_hundreds
- Dibstar
0
我刚刚将 MySql 中的一个函数从 999 更新到了 999999999。
这是原始链接：Reference
下面是更新后的版本：
CREATE FUNCTION `number_to_string`(n INT) RETURNS varchar(1000) CHARSET latin1 BEGIN
declare ans varchar(1000);
declare dig1, dig2, dig3,dig4,dig5,dig6,dig7,dig8,dig9 int;
set ans = '';
set dig9 = floor(n/100000000);
set dig8 = floor(n/10000000) - dig9*10;
set dig7 = floor(n/1000000) -(floor(n/10000000)*10);
set dig6 = floor(n/100000) - (floor(n/1000000)*10);
set dig5 = floor(n/10000) - (floor(n/100000)*10);
set dig4 = floor(n/1000) - (floor(n/10000)*10);
set dig3 = floor(n/100) - (floor(n/1000)*10);
set dig2 = floor(n/10) - (floor(n/100)*10);
set dig1 = n - (floor(n / 10)*10);
如果dig9大于0，则执行以下操作：
case
when dig9=1 then set ans=concat(ans, 'One Hundred');
when dig9=2 then set ans=concat(ans, 'Two Hundred');
when dig9=3 then set ans=concat(ans, 'Three Hundred');
when dig9=4 then set ans=concat(ans, 'Four Hundred');
when dig9=5 then set ans=concat(ans, 'Five Hundred');
when dig9=6 then set ans=concat(ans, 'Six Hundred');
when dig9=7 then set ans=concat(ans, 'Seven Hundred');
when dig9=8 then set ans=concat(ans, 'Eight Hundred');
when dig9=9 then set ans=concat(ans, 'Nine Hundred');
else set ans = ans;
end case;
if ans <> '' and dig8 =0 and dig7 = 0 then
set ans=concat(ans, ' Million');
end if;
end if;
if ans <> '' and dig8 > 0 then
set ans=concat(ans, ' And ');
end if;
if dig8 = 1 then
case
when (dig8*10 + dig7) = 10 then set ans=concat(ans,'Ten Million');
when (dig8*10 + dig7) = 11 then set ans=concat(ans,'Eleven Million');
when (dig8*10 + dig7) = 12 then set ans=concat(ans,'Twelve Million');
when (dig8*10 + dig7) = 13 then set ans=concat(ans,'Thirteen Million');
when (dig8*10 + dig7) = 14 then set ans=concat(ans,'Fourteen Million');
when (dig8*10 + dig7) = 15 then set ans=concat(ans,'Fifteen Million');
when (dig8*10 + dig7) = 16 then set ans=concat(ans,'Sixteen Million');
when (dig8*10 + dig7) = 17 then set ans=concat(ans,'Seventeen Million');
when (dig8*10 + dig7) = 18 then set ans=concat(ans,'Eighteen Million');
when (dig8*10 + dig7) = 19 then set ans=concat(ans,'Nineteen Million');
else set ans=ans;
end case;
else
if dig8 > 0 then
case
when dig8=2 then set ans=concat(ans, ' Twenty');
when dig8=3 then set ans=concat(ans, ' Thirty');
when dig8=4 then set ans=concat(ans, ' Fourty');
when dig8=5 then set ans=concat(ans, ' Fifty');
when dig8=6 then set ans=concat(ans, ' Sixty');
when dig8=7 then set ans=concat(ans, ' Seventy');
when dig8=8 then set ans=concat(ans, ' Eighty');
when dig8=9 then set ans=concat(ans, ' Ninety');
else set ans=ans;
end case;
if ans <> '' and dig7 =0 then
set ans=concat(ans, ' Million');
end if;
end if;
if ans <> '' and dig7 > 0 and dig8 =0 then
set ans=concat(ans, ' And ');
end if;
if dig7 > 0 then
case
when dig7=1 then set ans=concat(ans, ' One Million');
when dig7=2 then set ans=concat(ans, ' Two Million');
when dig7=3 then set ans=concat(ans, ' Three Million');
when dig7=4 then set ans=concat(ans, ' Four Million');
when dig7=5 then set ans=concat(ans, ' Five Million');
when dig7=6 then set ans=concat(ans, ' Six Million');
when dig7=7 then set ans=concat(ans, ' Seven Million');
when dig7=8 then set ans=concat(ans, ' Eight Million');
when dig7=9 then set ans=concat(ans, ' Nine Million');
else set ans = ans;
end case;
end if;
end if;
if ans <> '' and dig6 > 0 then
set ans=concat(ans, ' And ');
end if;
if dig6 > 0 then
case
when dig6=1 then set ans=concat(ans, 'One Hundred');
when dig6=2 then set ans=concat(ans, 'Two Hundred');
when dig6=3 then set ans=concat(ans, 'Three Hundred');
when dig6=4 then set ans=concat(ans, 'Four Hundred');
when dig6=5 then set ans=concat(ans, 'Five Hundred');
when dig6=6 then set ans=concat(ans, 'Six Hundred');
when dig6=7 then set ans=concat(ans, 'Seven Hundred');
when dig6=8 then set ans=concat(ans, 'Eight Hundred');
when dig6=9 then set ans=concat(ans, 'Nine Hundred');
else set ans = ans;
end case;
if ans <> '' and dig4 =0 and dig5 = 0 then
set ans=concat(ans, ' Thousand');
end if;
end if;
if ans <> '' and dig5 > 0 then
set ans=concat(ans, ' And ');
end if;
if dig5 = 1 then
case
when (dig5*10 + dig4) = 10 then set ans=concat(ans,'Ten Thousand');
when (dig5*10 + dig4) = 11 then set ans=concat(ans,'Eleven Thousand');
when (dig5*10 + dig4) = 12 then set ans=concat(ans,'Twelve Thousand');
when (dig5*10 + dig4) = 13 then set ans=concat(ans,'Thirteen Thousand');
when (dig5*10 + dig4) = 14 then set ans=concat(ans,'Fourteen Thousand');
when (dig5*10 + dig4) = 15 then set ans=concat(ans,'Fifteen Thousand');
when (dig5*10 + dig4) = 16 then set ans=concat(ans,'Sixteen Thousand');
when (dig5*10 + dig4) = 17 then set ans=concat(ans,'Seventeen Thousand');
when (dig5*10 + dig4) = 18 then set ans=concat(ans,'Eighteen Thousand');
when (dig5*10 + dig4) = 19 then set ans=concat(ans,'Nineteen Thousand');
else set ans=ans;
end case;
else
if dig5 > 0 then
case
when dig5=2 then set ans=concat(ans, ' Twenty');
when dig5=3 then set ans=concat(ans, ' Thirty');
when dig5=4 then set ans=concat(ans, ' Fourty');
when dig5=5 then set ans=concat(ans, ' Fifty');
when dig5=6 then set ans=concat(ans, ' Sixty');
when dig5=7 then set ans=concat(ans, ' Seventy');
when dig5=8 then set ans=concat(ans, ' Eighty');
when dig5=9 then set ans=concat(ans, ' Ninety');
else set ans=ans;
end case;
if ans <> '' and dig4 =0 then
set ans=concat(ans, ' Thousand');
end if;
end if;
if ans <> '' and dig4 > 0 and dig5 =0 then
set ans=concat(ans, ' And ');
end if;
if dig4 > 0 then
case
when dig4=1 then set ans=concat(ans, ' One Thousand');
when dig4=2 then set ans=concat(ans, ' Two Thousand');
when dig4=3 then set ans=concat(ans, ' Three Thousand');
when dig4=4 then set ans=concat(ans, ' Four Thousand');
when dig4=5 then set ans=concat(ans, ' Five Thousand');
when dig4=6 then set ans=concat(ans, ' Six Thousand');
when dig4=7 then set ans=concat(ans, ' Seven Thousand');
when dig4=8 then set ans=concat(ans, ' Eight Thousand');
when dig4=9 then set ans=concat(ans, ' Nine Thousand');
else set ans = ans;
end case;
end if;
end if;
if ans <> '' and dig3 > 0 then
set ans=concat(ans, ' And ');
end if;
if dig3 > 0 then
case
when dig3=1 then set ans=concat(ans, 'One Hundred');
when dig3=2 then set ans=concat(ans, 'Two Hundred');
when dig3=3 then set ans=concat(ans, 'Three Hundred');
when dig3=4 then set ans=concat(ans, 'Four Hundred');
when dig3=5 then set ans=concat(ans, 'Five Hundred');
when dig3=6 then set ans=concat(ans, 'Six Hundred');
when dig3=7 then set ans=concat(ans, 'Seven Hundred');
when dig3=8 then set ans=concat(ans, 'Eight Hundred');
when dig3=9 then set ans=concat(ans, 'Nine Hundred');
else set ans = ans;
end case;
end if;
if ans <> '' and dig2 > 0 then
set ans=concat(ans, ' And ');
end if;
if dig2 = 1 then
case
when (dig2*10 + dig1) = 10 then set ans=concat(ans,'Ten');
when (dig2*10 + dig1) = 11 then set ans=concat(ans,'Eleven');
when (dig2*10 + dig1) = 12 then set ans=concat(ans,'Twelve');
when (dig2*10 + dig1) = 13 then set ans=concat(ans,'Thirteen');
when (dig2*10 + dig1) = 14 then set ans=concat(ans,'Fourteen');
when (dig2*10 + dig1) = 15 then set ans=concat(ans,'Fifteen');
when (dig2*10 + dig1) = 16 then set ans=concat(ans,'Sixteen');
when (dig2*10 + dig1) = 17 then set ans=concat(ans,'Seventeen');
when (dig2*10 + dig1) = 18 then set ans=concat(ans,'Eighteen');
when (dig2*10 + dig1) = 19 then set ans=concat(ans,'Nineteen');
else set ans=ans;
end case;
else
if dig2 > 0 then
case
when dig2=2 then set ans=concat(ans, ' Twenty');
when dig2=3 then set ans=concat(ans, ' Thirty');
when dig2=4 then set ans=concat(ans, ' Fourty');
when dig2=5 then set ans=concat(ans, ' Fifty');
when dig2=6 then set ans=concat(ans, ' Sixty');
when dig2=7 then set ans=concat(ans, ' Seventy');
when dig2=8 then set ans=concat(ans, ' Eighty');
when dig2=9 then set ans=concat(ans, ' Ninety');
else set ans=ans;
end case;
end if;
if ans <> '' and dig1 > 0 and dig2 =0 then
set ans=concat(ans, ' And ');
end if;
if dig1 > 0 then
case
when dig1=1 then set ans=concat(ans, ' One');
when dig1=2 then set ans=concat(ans, ' Two');
when dig1=3 then set ans=concat(ans, ' Three');
when dig1=4 then set ans=concat(ans, ' Four');
when dig1=5 then set ans=concat(ans, ' Five');
when dig1=6 then set ans=concat(ans, ' Six');
when dig1=7 then set ans=concat(ans, ' Seven');
when dig1=8 then set ans=concat(ans, ' Eight');
when dig1=9 then set ans=concat(ans, ' Nine');
else set ans=ans;
end case;
end if;
end if;
return trim(ans);
END
我这样测试它：选择 number_to_string(999999999)
结果是：'九亿九千九百九十九万九千九百九十九'
- Mohamed Al Kurwi
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接