如何将数字转换为文本货币到文本 [英] How to convert number into text currency to text
本文介绍了如何将数字转换为文本货币到文本的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
任何人都可以帮助将金额ex 410016转换为四个Lac十一十六只在sql
解决方案
>
请阅读我的评论。
看看这里:
http://techcreeze.blogspot.com/2008/11/convert-amount-into-words-according-to.html [ ^ ]
http:// www.dotnetspider.com/forum/314884-Convert-Decimal-Value-To-Words.aspx [ ^ ]
HTTP://www.nul lskull.com/q/10381222/procedure-or-function-to-convert-decimal-amount-in-words.aspx [ ^ ]
alter FUNCTION [dbo]。[Currency_ToWords](
@输入 数字( 38 , 3 ) - 输入数字最多为18位
) RETURNS VARCHAR ( 8000 )
/ *
*转换整数
*等价词中的最大值为34位数。第一个字母是大写的。
*
*归因:基于Srinivas Sampath的NumberToWords
*由Nick Barclay修改
*
*示例:
select dbo.udf_Num_ToWords(1234567890) )+ CHAR(10)
+ dbo.udf_Num_ToWords(0)+ CHAR(10)
+ dbo.udf_Num_ToWords(123)+ CHAR(10)
select dbo.udf_Num_ToWords(76543210987654321098765432109876543210)
DECLARE @i numeric(38,0)
SET @i = 0
WHILE @I< = 1000 BEGIN
PRINT convert(char(5),@ i)
+ convert(varchar(255),dbo.udf_Num_ToWords(@i))
SET @I = @i + 1
END
*
*已发布作为本周的T-SQL UDF第2卷#9 2/17/03
**************************** ************************************ /
AS BEGIN
声明 @ Number 数字( 38 , 0 )
set @ Number = < span class =code-sdkkeyword> @ Input
声明 @ Cents as int
set < span class =code-sdkkeyword> @ Cents = 100 *转换( money ,( @ Input - convert (数字( 38 , 3 ), @ Number )))
DECLARE @ inputNumber VARCHAR ( 38 )
DECLARE @ NumbersTable TABLE (number CHAR ( 2 ),word VARCHAR ( 10 ))
DECLARE @ outputString VARCHAR ( 8000 )
DECLARE @ length INT
DECLARE @ counter INT
DECLARE @ loops INT
DECLARE @ position INT
DECLARE @ chunk CHAR ( 3 ) - 对于3个数字的块
DECLARE @ tensones CHAR ( 2 )
DECLARE @ hundred CHAR ( 1 )
DECLARE @ tens CHAR ( 1 )
DECLARE @ ones CHAR ( 1 )
IF @ Number = 0 返回 零'
- 初始化变量
SELECT @ inputNumber = CONVERT ( varchar ( 38 ), @ Number )
, @ outputString = ' '
, @ counter = 1
SELECT @ length = LEN( @ inputNumber )
, @ position = LEN( @ inputNumber ) - 2
, @ loops = LEN( @ inputNumber )/ 3
- - 确保为剩余数字添加额外的循环
IF LEN( @ inputNumber )% 3 <> 0 SET @ loops = @ loops + 1
- 插入数字和单词的数据
INSERT INTO @ NumbersTable SELECT ' 00',' '
UNION ALL SELECT ' 01',' 一个' UNION 所有 SELECT ' 02',' two'
UNION ALL SELECT ' 03',' 三个 UNION ALL SELECT ' 04',' four'
UNION ALL SELECT ' 05',' 五'UNION ALL SELECT ' 06',' 6'
UNION ALL SELECT ' 07',' seven' UNION ALL < span class =code-keyword> SELECT ' 08', 8'
UNION ALL SELECT ' 09' 跨度>, ' nine' UNION ALL SELECT ' 10',' ten'
UNION ALL SELECT ' 11',' 11'' UNION ALL SELECT ' 12',' 12'
UNION ALL SE LECT ' 13',' 十三' UNION 所有 SELECT ' 14',' 十四'
UNION ALL SELECT ' 15',' 十五' UNION ALL SELECT ' 16',' 16'
UNION ALL SELECT ' 17',' 十七' UNION ALL SELECT ' 18',' 十八'
UNION ALL SELECT ' 19', ' 十九' UNION 所有 SELECT ' 20',' 20'
UNION ALL SELECT ' 30',' 30' UNION ALL SELECT ' 40',' foury'
UNION ALL SELECT ' 50',' 50' UNION ALL SELECT < /跨度> ' 60',' sixty'
UNION 所有 SELECT ' 70',' 七十' UNION ALL SELECT ' 80',' eighty'
UNION ALL SELECT ' 90',' ninety'
WHILE @ counter < = @ loops BEGIN
- 一次获取3个数字的块,用前导零填充
SET @ chunk = RIGHT (' 000' + SUBSTRING( @ inputNumber , @ position , 3 ), 3 )
IF @ chunk <> ' 000' BEGIN
SELECT @ tensones = SUBSTRING( @ chunk , 2 , 2 )
, @ hundred = SUBSTRING( @ chunk , 1 , 1 )
, @ tens = SUBSTRING( @ chunk , 2 , 1 )
, @ ones = SUBSTRING( @ chunk , 3 , 1 )
- 如果二十或更少,直接使用@NumbersTable
IF CONVERT ( INT , @ tensones )< = 20 OR @ Ones = ' 0' BEGIN
SET @ outputString =( SELECT 字
FROM @ NumbersTable
WHERE @ tensones = number)
+ CASE @ counter WHEN 1 那么 ' ' - 无名称
WHEN 2 那么 < span class =code-string>' 千' WHEN 3 那么 ' 百万'
WHEN 4 那么 ' 十亿' WHEN 5 那么 ' trillion'
WHEN 6 那么 ' quadrillion' WHEN 7 那么 < span class =code-string>' quintillion'
WHEN < span class =code-digit> 8 那么 ' sextillion' WHEN 9 那么 ' septillion'
WHEN 10 THEN ' octillion' WHEN 11 那么 ' nonillion'
WHEN 12 那么 ' decillion' WHEN 13 那么 ' undecillion'
ELSE ' ' END
+ @ outputString
END
ELSE BEGIN < span class =code-comment> - 分别分解那些和几十个
SET @ outputString = ' '
+( SELECT 字
FROM @ NumbersTable
WHERE @ tens + ' 0' = number)
+ ' - '
+(SELECT 字
FROM @ NumbersTable
WHERE ' 0' + @ ones = number)
+ CASE @counter WHEN 1 那么 ' ' - 无名称
WHEN 2 那么 ' 千'' WHE N 3 那么 ' million'
WHEN 4 那么 ' 亿' WHEN 5 那么 ' trillion'
WHEN 6 那么 ' quadrillion' WHEN 7 那么 ' quintillion'
WHEN 8 那么 ' sextillion' WHEN 9 THEN ' septillion'
WHEN 10 那么 ' octillion' WHEN 11 那么 ' nonillion'
WHEN 12 那么 ' decillion' WHEN 13 THEN ' undecillion'
ELSE ' ' END
+ @ outputString
END
- 现在获得数百
IF @ hundred <> ' 0' BEGIN
SET @ outputString =( SELECT 字
FROM @ NumbersTable
WHERE ' 0' + @ hundred = number)
+ ' 100'
+ @ outputString
END
END
SELECT @ counter = @ counter + 1
, @ position = @ position - 3
END
- 删除任何双重空格
SET @ outputString = LTRIM(RTRIM (REPLACE( @ outputString ,' ' ,' ')))
SET @ outputstring = UPPER( LEFT ( @ outputstring , 1 ))+ SUBSTRING( @ output string , 2 , 8000 )
RETURN UPPER( @ outputString )+ ' ONLY' - + convert(Varchar(20), @Cents)+'/ 100 CENTS' - 返回结果
END
- SELECT dbo。[Currency_ToWords]('4444.44')
>
can any one help to convert amount ex 410016 to Four Lac Ten Thousand sixteen Only
in sql
解决方案
Please, read my comment.
Have a look here:
http://techcreeze.blogspot.com/2008/11/convert-amount-into-words-according-to.html[^]
http://www.dotnetspider.com/forum/314884-Convert-Decimal-Value-To-Words.aspx[^]
http://www.nullskull.com/q/10381222/procedure-or-function-to-convert-decimal-amount-in-words.aspx[^]
alter FUNCTION [dbo].[Currency_ToWords] ( @Input Numeric (38, 3) -- Input number with as many as 18 digits ) RETURNS VARCHAR(8000) /* * Converts a integer number as large as 34 digits into the * equivalent words. The first letter is capitalized. * * Attribution: Based on NumberToWords by Srinivas Sampath * as revised by Nick Barclay * * Example: select dbo.udf_Num_ToWords (1234567890) + CHAR(10) + dbo.udf_Num_ToWords (0) + CHAR(10) + dbo.udf_Num_ToWords (123) + CHAR(10) select dbo.udf_Num_ToWords(76543210987654321098765432109876543210) DECLARE @i numeric (38,0) SET @i = 0 WHILE @I <= 1000 BEGIN PRINT convert (char(5), @i) + convert(varchar(255), dbo.udf_Num_ToWords(@i)) SET @I = @i + 1 END * * Published as the T-SQL UDF of the Week Vol 2 #9 2/17/03 ****************************************************************/ AS BEGIN Declare @Number Numeric(38,0) set @Number = @Input Declare @Cents as int set @Cents = 100*Convert(money,(@Input - convert(Numeric(38,3),@Number))) DECLARE @inputNumber VARCHAR(38) DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10)) DECLARE @outputString VARCHAR(8000) DECLARE @length INT DECLARE @counter INT DECLARE @loops INT DECLARE @position INT DECLARE @chunk CHAR(3) -- for chunks of 3 numbers DECLARE @tensones CHAR(2) DECLARE @hundreds CHAR(1) DECLARE @tens CHAR(1) DECLARE @ones CHAR(1) IF @Number = 0 Return 'Zero' -- initialize the variables SELECT @inputNumber = CONVERT(varchar(38), @Number) , @outputString = '' , @counter = 1 SELECT @length = LEN(@inputNumber) , @position = LEN(@inputNumber) - 2 , @loops = LEN(@inputNumber)/3 -- make sure there is an extra loop added for the remaining numbers IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1 -- insert data for the numbers and words INSERT INTO @NumbersTable SELECT '00', '' UNION ALL SELECT '01', 'one' UNION ALL SELECT '02', 'two' UNION ALL SELECT '03', 'three' UNION ALL SELECT '04', 'four' UNION ALL SELECT '05', 'five' UNION ALL SELECT '06', 'six' UNION ALL SELECT '07', 'seven' UNION ALL SELECT '08', 'eight' UNION ALL SELECT '09', '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', 'ninety' WHILE @counter <= @loops BEGIN -- get chunks of 3 numbers at a time, padded with leading zeros SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3) IF @chunk <> '000' BEGIN SELECT @tensones = SUBSTRING(@chunk, 2, 2) , @hundreds = SUBSTRING(@chunk, 1, 1) , @tens = SUBSTRING(@chunk, 2, 1) , @ones = SUBSTRING(@chunk, 3, 1) -- If twenty or less, use the word directly from @NumbersTable IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN SET @outputString = (SELECT word FROM @NumbersTable WHERE @tensones = number) + CASE @counter WHEN 1 THEN '' -- No name WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million ' WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion ' WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion ' WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion ' WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion ' WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion ' ELSE '' END + @outputString END ELSE BEGIN -- break down the ones and the tens separately SET @outputString = ' ' + (SELECT word FROM @NumbersTable WHERE @tens + '0' = number) + '-' + (SELECT word FROM @NumbersTable WHERE '0'+ @ones = number) + CASE @counter WHEN 1 THEN '' -- No name WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million ' WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion ' WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion ' WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion ' WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion ' WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion ' ELSE '' END + @outputString END -- now get the hundreds IF @hundreds <> '0' BEGIN SET @outputString = (SELECT word FROM @NumbersTable WHERE '0' + @hundreds = number) + ' hundred ' + @outputString END END SELECT @counter = @counter + 1 , @position = @position - 3 END -- Remove any double spaces SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' '))) SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000) RETURN UPPER(@outputString) + ' ONLY'-- +convert(Varchar(20),@Cents) + '/100 CENTS'-- return the result END --SELECT dbo.[Currency_ToWords] ('4444.44')
这篇关于如何将数字转换为文本货币到文本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文