MS SQL中Word中的金额 [英] Amount in Word in MS SQL
本文介绍了MS SQL中Word中的金额的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我可以建议如何在MS SQL中转换单词量，请尽快给我回复。
提前谢谢！
Can me suggest how can convert amount in word in MS SQL, Pls send me reply by ASAP.
Thanks in advance!
推荐答案
嗨..
首先创建此表，
Hi..
First Create this table,
CREATE TABLE [dbo].[M_Words](
[Code] [int] NULL,
[WNumber] [int] NULL,
[Wwords] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
然后
then
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(1,0,'Zero')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(2,1,'One')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(3,2,'Two')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(4,3,'Three')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(5,4,'Four')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(6,5,'Five')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(7,6,'Six')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(8,7,'Seven')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(9,8,'Eight')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(10,9,'Nine')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(11,10,'Ten')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(12,11,'Eleven')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(13,12,'Twelve')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(14,13,'Thirteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(15,14,'Fourteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(16,15,'Fifteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(17,16,'Sixteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(18,17,'Seventeen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(19,18,'Eighteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(20,19,'Nineteen')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(21,20,'Twenty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(22,30,'Thirty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(23,40,'Forty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(24,50,'Fifty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(25,60,'Sixty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(26,70,'Seventy')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(27,80,'Eighty')
INSERT INTO [M_Words] ([Code],[WNumber],[Wwords])VALUES(28,90,'Ninety')
然后写这个函数
then write this Function
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create function [dbo].[usp_fnNumToWords](@Number Numeric(18,2),@CPaise Char(1))
returns varchar(5000)
BEGIN
Declare @StrNumber varchar(10), @SLacs char(2), @SThou char(2), @SHun char(2)
Declare @STenUnt char(2), @STen char(2), @SUnt char(2), @SDecimal char(2)
Declare @ILacs Int, @IThou Int, @IHun Int, @ITenUnt Int, @ITen Int, @IUnt Int, @IDecimal Int
Declare @SNumToWords varchar(100), @Wwords varchar(10)
Select @StrNumber = Replicate('0',10-Len(LTrim(RTrim(convert(varchar,@Number))))) + LTrim(RTrim(Convert(varchar,@Number)))
--Print @StrNumber
--Print Len(@StrNumber)
Select @SNumToWords = ''
--Print Len(LTrim(RTrim(convert(varchar,@Number))))
If Len(LTrim(RTrim(convert(varchar,@Number)))) > 4
Begin
--Print Len(@StrNumber)
Select @SLacs = Substring(@StrNumber,1,2)
--Print @SLacs
Select @ILacs = Convert(int,@SLacs)
If @ILacs > 0
Begin
Select @STen = Substring(@StrNumber,1,1)
Select @SUnt = Substring(@StrNumber,2,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,1,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
--Select @Wwords = ''
--Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
--Print @Wwords
Select @SNumToWords = @SNumToWords + ' Lacs'
End
Select @SThou = Substring(@StrNumber,3,2)
--Print @SThou
Select @IThou = Convert(int,@SThou)
If @IThou > 0
Begin
Select @STen = Substring(@StrNumber,3,1)
Select @SUnt = Substring(@StrNumber,4,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,3,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
--Select @Wwords = ''
--Select @Wwords = Wwords From M_Words Where WNumber = @IThou
--Print @Wwords
--Select @SNumToWords = @SNumToWords + @Wwords + ' Thousand '
Select @SNumToWords = @SNumToWords + ' Thousand '
End
Select @SHun = Substring(@StrNumber,5,1)
--Print @SHun
Select @IHun = Convert(int,@SHun)
If @IHun > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IHun
--Print @Wwords
Select @SNumToWords = @SNumToWords + @Wwords + ' Hundred'
End
Select @STenUnt = Substring(@StrNumber,6,2)
---Print @STenUnt
Select @ITenUnt = Convert(int,@STenUnt)
If @ITenUnt > 0
Begin
Select @STen = Substring(@StrNumber,6,1)
Select @SUnt = Substring(@StrNumber,7,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,6,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
End
Select @SNumToWords = @SNumToWords + Space(1) + 'Rupees' --Only/-
End
Else
Begin
--Print Len(@StrNumber)
--Print LTrim(RTrim(convert(varchar,@Number)))
Select @SLacs = Substring(LTrim(RTrim(convert(varchar,@Number))),1,1)
--Print @SLacs
Select @ILacs = Convert(int,@SLacs)
If @ILacs > 0 and @ILacs <> 1
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords + Space(1) + 'Rupees'
End
Else
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
--Print @Wwords
Select @SNumToWords = @SNumToWords + @Wwords + Space(1) + 'Rupee'
End
End
If @CPaise = 'Y'
Begin
Select @SDecimal = Substring(@StrNumber,9,2)
Select @IDecimal = Convert(int,@SDecimal)
If @IDecimal > 0
Begin
Select @SNumToWords = @SNumToWords + ' and'
Select @STen = Substring(@SDecimal,1,1)
Select @SUnt = Substring(@SDecimal,2,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,9,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
Select @SNumToWords = @SNumToWords + Space(1) + 'Paise'
End
End
--Print LTrim(RTrim(@SNumToWords))
return LTrim(RTrim(@SNumToWords))
END
请选择这样
select like this
select dbo.usp_fnNumToWords(105422.00 ,'Y')
Y - 用ps
N-without ps
问候，
pal
Y- with ps
N-without ps
Regards,
pal
< b>步骤1：< / b>创建一个函数
将以下文本复制到查询分析器并执行它
/ ************ ************************************************** ***************** /
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO
创建功能dbo.Num_ToWords（@Number Numeric（38,0））
RETURNS VARCHAR（8000）
AS BEGIN
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 @hndreds CHAR（1）
DECLARE @tens CHAR（1）
DECLARE @ONE CHAR（1）
IF @Number = 0返回'零'
- 初始化变量
SELECT @inputNumber = CONVERT（varchar（38），@ Number）
，@ outputString =''
，@ counter = 1
SELECT @length = LEN（@inputNumber）
，@ position = LEN（@inputNumber） - 2
，@ lopops = LEN（@inputNumber）/ 3
- 确保为剩余的数字添加额外的循环
如果LEN（@inputNumber）％3<> 0 SET @loops = @loops + 1
- 插入数字和单词的数据
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 '，'七'UNION ALL SELECT'08'，'八'
UNION ALL SELECT'09'，'九'UNION ALL SELECT'10'，'ten'
UNION ALL SELECT'11'，'十一'UNION ALL SELECT'12'，'十二'
UNION ALL SELECT'13'，'十三'UNION ALL SELECT'14'，'十四'
UNION ALL SELECT'15'，'十五'UNION ALL SELECT'16'，'十六'
UNION ALL SELECT'17'，'seventeen'UNION ALL SELECT '18 '，''十八'
UNION ALL SELECT'19'，'十九'UNION ALL SELECT'20'，'二十'
UNION ALL SELECT'30'，'三十'UNION ALL SELECT'40'，'四十'
UNION ALL SELECT'50'，'五十'UNION ALL SELECT'60'，'sixty'
UNION ALL SELECT '70'，'七十'UNION ALL SELECT'80'，'八十'
UNION ALL SELECT'90'，'九十'
WHILE @counter< = @loops BEGIN
- 一次获取3个数字的块，用前导零填充
SET @chunk = RIGHT（'000'+ SUBSTRING（@inputNumber，@ position，3），3）
IF @chunk<> '000'BEGIN
SELECT @tensones = SUBSTRING（@chunk，2,2）
，@ hundreds = SUBSTRING（@chunk，1,1）
，@ lt = SUBSTRING（@chunk，2,1）
，@ ans = SUBSTRING（@chunk，3,1）
- 如果是20或者更少，请直接使用@NumbersTable中的单词
IF CONVERT（INT，@tensones）< = 20 OR @Ones ='0'BEGIN
SET @outputString =（SELECT word
FROM @NumbersTable
WHERE @tensones = number）
+ CASE @counter WHEN 1然后' - 没有名字
当2'那么'千'时3''百万'
当4亿'十亿'时5'那么'万亿'
当6'那么'千万'当7'那么'quintillion'
当8'那么'sextillio n'当9''septillion'
当10'那么'octillion'当11'那么'nonillion'
当12'那么'十亿''13'那么'十亿''br />
ELSE''END
+ @outputString
END
ELSE BEGIN - 分别分解那些和十个
SET @outputString =''
+（SELECT word
FROM @NumbersTable
WHERE @tens +'0'=数字）
+' - '
+（SELECT word
FROM @NumbersTable
WHERE'0'+ @ones =数字）
+ CASE @counter WHEN 1那么' - 没有名字
当2'那么'千'时3''百万'
当4亿'十亿'时5'那么'万亿'
当6'那么'千万''当7''quintillion'
当8'那时'sextillion'当9''septillion'
当10'那么'octillion'时11那么'nonillion'
当12'那么'十亿'时13'然后'十亿'
ELSE''结束
+ @outputString
结束
- 现在获得数百元
IF @hundreds<> '0'开始
SET @outputString =（SELECT word
FROM @NumbersTable
WHERE'0'+ @hundreds = number）
+'百'
+ @outputString
结束
结束
SELECT @counter = @counter + 1
，@ position = @position - 3
END
- 删除所有双倍空格
SET @outputString = LTRIM（RTRIM（REPLACE（@outputString，''，''）））
SET @outputstring = UPPER（LEFT（@outputstring，1））+ SUBSTRING（@outputstring，2,8000）
RETURN @outputString - 返回结果
结束
GO
dbo上的GRANT EXEC。 udf_Num_ToWords to PUBLIC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/ ********************************* ********************************************** /
< b>第2步：< / b>执行查询
选择dbo.Num_ToWords（1234）
< b>结果：< / b>一千二百三十四
<b>Step 1:</b> Create a function
Copy the below text into Query analyzer and execute it
/*******************************************************************************/
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO
CREATE FUNCTION dbo.Num_ToWords (@Number Numeric (38, 0))
RETURNS VARCHAR(8000)
AS BEGIN
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 @outputString -- return the result
END
GO
GRANT EXEC on dbo.udf_Num_ToWords TO PUBLIC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*******************************************************************************/
<b>Step 2:</b> Execute the query
select dbo.Num_ToWords (1234)
<b>Result:</b> One thousand two hundred thirty-four
这篇关于MS SQL中Word中的金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
