Convert Money to Text
-
I am trying to create a report that will allow the printing of checks. The amount to be paid is data type money but I also need to write it out on the check line. You know, 25,010 written out as "Twenty Five Thousand and Ten".
Any help would be appreciated!
-
You know you either have or do not have comma's.
I would suggest you make a table with the text, (i.e., 25 = "Twenty Five", 250 = "Two hundred and fifty", or something like that).
Then create a function which will strip apart each section of the money as text. Depending upon how many comma's, you will know if you have thousands, millions, hundreds, etc).
Dump each of those records into a @Variable and keep adding to that @Variable until you have completed your text...
I wasn't born stupid - I had to study.
-
Found this...
It won't give you the decimal but it's close------------------------------Create table -----------------------------------Create table dbo.NumberText (N decimal(28,0) primary key,
C bit,
S varchar(40)
)
insert into NumberText values (0,0,'zero')
insert into NumberText values (1,0,'one')
insert into NumberText values (2,0,'two')
insert into NumberText values (3,0,'three')
insert into NumberText values (4,0,'four')
insert into NumberText values (5,0,'five')
insert into NumberText values (6,0,'six')
insert into NumberText values (7,0,'seven')
insert into NumberText values (8,0,'eight')
insert into NumberText values (9,0,'nine')
insert into NumberText values (10,0,'ten')
insert into NumberText values (11,0,'eleven')
insert into NumberText values (12,0,'twelve')
insert into NumberText values (13,0,'thirteen')
insert into NumberText values (14,0,'fourteen')
insert into NumberText values (15,0,'fifteen')
insert into NumberText values (16,0,'sixteen')
insert into NumberText values (17,0,'seventeen')
insert into NumberText values (18,0,'eighteen')
insert into NumberText values (19,0,'nineteen')
insert into NumberText values (20,0,'twenty')
insert into NumberText values (30,0,'thirty')
insert into NumberText values (40,0,'forty')
insert into NumberText values (50,0,'fifty')
insert into NumberText values (60,0,'sixty')
insert into NumberText values (70,0,'seventy')
insert into NumberText values (80,0,'eighty')
insert into NumberText values (90,0,'ninety')
insert into NumberText values (100,1,'hundred')
insert into NumberText values (1000,1,'thousand')
insert into NumberText values (1000000,1,'million')
insert into NumberText values (1000000000,1,'billion')
insert into NumberText values (1000000000000,1,'trillion')
insert into NumberText values (1000000000000000,1,'quadrillion')
insert into NumberText values (1000000000000000000,1,'quintillion')
GO
------------------------------Create Function-----------------------------------Create function dbo.ConvertCurrencyToWords(
@i bigint
) returns nvarchar(1000) as
begindeclare @s-2 nvarchar(1000),@Money varchar(50)declare @n bigint, @left bigint, @right bigint, @C tinyint,@decimal int
-- find biggest value in table less than
-- or equal to the number to translate
-- into words:
select top 1 @s-2 = S, @n = N, @C = Cfrom dbo.NumberText
where N <= @i
order by N desc
if @n = 0
return @s-2
set @left = @i/@n -- "how many"set @right = @i%@n -- "what's left"
/* if(charindex('.',@Money) > 0)set @s-2=@s + ' AND ' + substring(@Money,@decimal + 1,2) +'/100 DOLLARS ***'*/
return @s-2end
Select dbo.ConvertCurrencyToWords (2016.05) -
I've used this method before to do this:
-
Cedar72,
Good find... I'm amazed at how comparitively fast this one is compared to the other methods I've seen (recursion is cool)... returns 10000 records in a little over 3 seconds (still a bit slow for a function but maybe not for what it does) whereas the other methods I've seen so far take 25 to 30 seconds for the same task.
Just a note for everyone... I've found that the LTRIM/RTRIM functions aren't necessary and may be safely removed (less to do so increases speed a bit more).
And, for check writing purposes or displaying a spelled out year, I'll capitalize the first character of each word to make things pretty.
I'm playing with building a hibrid between a couple of the methods that I've found so far to see if I can get the speed up and I'll post it if it's faster. So far, I haven't been able to make one any faster than what Cedar72 found.
Thanks again for the post, Cedar...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Lucky you, if you only have to work with such a simple language as English. In Czech language, the words like "hundred" or "thousand" have different forms depending on whether you are saying one, two, three etc. - not mentioning the fact that even "one" is different, depending on whether it is one thousand or one hundred. So, for example:
one hundred = "jedno sto"
three hundred = "tri sta"
seven hundred = "sedm set"
one thousand = "jeden tisic"
three thousands = "tri tisice"
seven thousands = "sedm tisic"
We're using it when printing invoices (the part that can be used to pay at post office), but fortunately it is implemented outside SQL in the printing software and we didn't have to bother about that. Generally, it works similarly to what Cedar posted, just the configuration table has more rows and the decision making is more complicated.
PS: Sorry for partially off-topic post, I just thought it could be interesting to know what things other people have to face... and Cedar's post seems to solve your problem well enough, so that I can't add anything of more value.
-
That's ok... I guess I'm pretty happy that American English is my native language
except for the fact that words like "Dude" have about 32 different meanings depending on how you say it 
On the serious side, I notice that, in your example, the Czech word for "thousands" is spelled differently for "three thousands" than it is for "seven thousands"... is that a typo or does it really do that?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That's right, it is spelled differently and it is not a typo. Czech language uses declination, which is a thing that complicates it a lot for foreigners. Latin has 6 , German has 4... and we have 7 forms of each noun for singular and 7 for plural (some of them are equal most of the time, but anyway).
When counting, you use nominative of plural with 2, 3 and 4. With 5 and more you have to use genitive of plural. (If you say 124, the form is the same as with 4 - always the way you read it matters, if you say "four" at the end, the form of following noun is nominative). In fact, it is a bit different with thousands, but I won't overcomplicate the situation. How it originated, I have no clue - it is natural for us to say so. Historically, Czech language had even a special set of forms to go with "two" - there was singular, dual and plural - but that's several hundred years ago. The only book where you can find it today is re-edition of bible in medieval translation :-).
Well, pitfall of Czech language are the countless forms... pitfall of English language is pronunciation (there can be no mistake how to pronounce a Czech word, once you know how to pronounce each letter) and many different meanings of the same word, even if you don't consider slang.
-
Vladan,
Thank you very much for taking the time to explain all this because it's very interesting. I do have to ask... are you a linguist or just really well informed about the language? Me... I can't tell a prepositional phrase from a conjugated verb.
My Grand-Mother was Czech... She spoke the "Slovak" language. Is that the same as Czech or is it more on the Bohemian side?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
He is just living there.
We are all linguists in this part of Europe.
Vladan you did not mention about unit names.
Not sure about Czech, but in Ukrainian it will be (transliterated from Cyrillic, of course):
... one dollar - odyn dollar
... two dollars - dva dollary
... five dollars - p'yat' dollariv
... hundred dollars - sto dollariv
If you say "missing", "there is no" -
... one dollar - odogo dollara
... two dollars - dvoh dollariv
... five dollars - p'yat'oh dollariv
... hundred dollars - sta dollariv
For Hryvna (or Krona, if you wish) :
... one hryvna- odna hryvna
... two hryvnas - dvi hryvni
... five hryvnas - p'yat' hryven'
... four hundred fourty four hryvnas - chotyrysta sorok chotyry hryvni
If you say "missing", "there is no" -
... one hryvna- odnieji hryvni
... two hryvnas - dvoh hryven'
... five hryvnas - p'yat'oh hryven'
... four hundred fourty four hryvnas - chotyr'ohsot soroka chotyrioh hryven'
And so on.
_____________
Code for TallyGenerator -
Daaaannnngggg.... Guess I'll stick with plain ol' English, SQL, and some pig-latin....
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, Sergiy, it is very similar in Czech - the counted unit is changing, too.
Jeff, I think I know a bit more about languages and grammatics than the average people, because it always interested me a lot, but I'm not a linguist.
Slovak and Czech are two different languages (not sure what you mean by Bohemian, because Bohemia is latin word for Czechia), but very very close. In fact, I think that many languages have dialects, that are less similar to each other than Czech and Slovak language. I've grown up in the times of Czechoslovakia, when about 30% of programs in the TV and radio were in Slovak, so I can hardly consider Slovak a foreign language... but I've observed, that younger Czech people sometimes have problems understanding Slovak - but only in the sense that they don't know some words. There always is a good general understanding. I speak Czech, they reply in Slovak, no problem on any side.
BTW, there is also a dialect of Czech language, name of which could be translated into English as "Slovak", so I'm not sure whether your grandmother was Slovak, or Czech from the part using this dialect (it is on the borders with Slovakia, so there is hardly any difference in it for you :-)).
-
This is the method I use orignally was posted as a Stored Procedure by me here
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=259
but this is the Function version.
--You will need this table.--
CREATE TABLE NumNameTbl (
[val] [int] NOT NULL,
[numname] [varchar](20) NOT NULL,
CONSTRAINT [PK_NumNameTbl_val] PRIMARY KEY CLUSTERED
(
[val]
 
WITH FILLFACTOR = 100)
GO
/* Insert the value records, yes this is a heap table */
INSERT INTO NumNameTbl (val,numname) VALUES (1,'One')
INSERT INTO NumNameTbl (val,numname) VALUES (2,'Two')
INSERT INTO NumNameTbl (val,numname) VALUES (3,'Three')
INSERT INTO NumNameTbl (val,numname) VALUES (4,'Four')
INSERT INTO NumNameTbl (val,numname) VALUES (5,'Five')
INSERT INTO NumNameTbl (val,numname) VALUES (6,'Six')
INSERT INTO NumNameTbl (val,numname) VALUES (7,'Seven')
INSERT INTO NumNameTbl (val,numname) VALUES (8,'Eight')
INSERT INTO NumNameTbl (val,numname) VALUES (9,'Nine')
INSERT INTO NumNameTbl (val,numname) VALUES (10,'Ten')
INSERT INTO NumNameTbl (val,numname) VALUES (11,'Eleven')
INSERT INTO NumNameTbl (val,numname) VALUES (12,'Twelve')
INSERT INTO NumNameTbl (val,numname) VALUES (13,'Thirteen')
INSERT INTO NumNameTbl (val,numname) VALUES (14,'Fourteen')
INSERT INTO NumNameTbl (val,numname) VALUES (15,'Fifteen')
INSERT INTO NumNameTbl (val,numname) VALUES (16,'Sixteen')
INSERT INTO NumNameTbl (val,numname) VALUES (17,'Seventeen')
INSERT INTO NumNameTbl (val,numname) VALUES (18,'Eightteen')
INSERT INTO NumNameTbl (val,numname) VALUES (19,'Nineteen')
INSERT INTO NumNameTbl (val,numname) VALUES (20,'Twenty')
INSERT INTO NumNameTbl (val,numname) VALUES (30,'Tirty')
INSERT INTO NumNameTbl (val,numname) VALUES (40,'Fourty')
INSERT INTO NumNameTbl (val,numname) VALUES (50,'Fifty')
INSERT INTO NumNameTbl (val,numname) VALUES (60,'Sixty')
INSERT INTO NumNameTbl (val,numname) VALUES (70,'Seventy')
INSERT INTO NumNameTbl (val,numname) VALUES (80,'Eighty')
INSERT INTO NumNameTbl (val,numname) VALUES (90,'Ninty')
GO
/* The Function. */
CREATE FUNCTION dbo.MoneyName(@Money Money)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @MonName VARCHAR(8000)
DECLARE @MonStr VARCHAR(100)
DECLARE @TempMon VARCHAR(3)
SET @MonStr = CAST(@Money AS VARCHAR(100))
SET @MonName = ''
IF CAST(RIGHT(@MonStr,2) AS INT) > 0
BEGIN
SET @TempMon = RIGHT(@MonStr,2)
SET @MonName = 'Cents'
SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(@TempMon,1) WHEN 1 THEN @TempMon ELSE RIGHT(@TempMon,1) END)
IF LEFT(@TempMon,1) NOT IN (0,1)
SELECT @MonName = numname + (CASE LTRIM(@MonName) WHEN 'CENTS' THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(@TempMon,1) + '0') AS int)
IF LEN(@MonStr) = 4 AND LEFT(@MonStr,1) = 0
BEGIN
SET @MonName = @MonName
END
ELSE
SET @MonName = 'And ' + @MonName
END
SET @MonStr = LEFT(@MonStr,LEN(@MonStr) - 3)
/* Ones, Tens Hundreds */
IF LEN(@MonStr) > 0
BEGIN
SET @TempMon = RIGHT(@MonStr,3)
SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))
If CAST(@TempMon AS INT) != 0
BEGIN
SET @MonName = 'Dollars ' + @MonName
SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
IF LEN(@TempMon) > 1
BEGIN
IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
END
IF CHARINDEX('Dollars',@MonName) > 1 AND (LEN(@TempMon) = 3 OR LEN(@MonStr) > 0)
IF CHARINDEX(' And ',@MonName) = 0
SET @MonName = 'And ' + @MonName
IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
END
END
/* Thousands */
IF LEN(@MonStr) > 0
BEGIN
SET @TempMon = RIGHT(@MonStr,3)
SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))
If CAST(@TempMon AS INT) != 0
BEGIN
IF CHARINDEX('Dollars',@MonName) = 0
SET @MonName = 'Dollars ' + @MonName
SET @MonName = 'Thousand ' + @MonName
SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
IF LEN(@TempMon) > 1
BEGIN
IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
END
IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
END
END
/* Million */
IF LEN(@MonStr) > 0
BEGIN
SET @TempMon = RIGHT(@MonStr,3)
SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))
If CAST(@TempMon AS INT) != 0
BEGIN
IF CHARINDEX('Dollars',@MonName) = 0
SET @MonName = 'Dollars ' + @MonName
SET @MonName = 'Million ' + @MonName
SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
IF LEN(@TempMon) > 1
BEGIN
IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
END
IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
END
END
/* Billion */
IF LEN(@MonStr) > 0
BEGIN
SET @TempMon = RIGHT(@MonStr,3)
SET @MonStr = LEFT(@MonStr, LEN(@MonStr) - LEN(@TempMon))
If CAST(@TempMon AS INT) != 0
BEGIN
IF CHARINDEX('Dollars',@MonName) = 0
SET @MonName = 'Dollars ' + @MonName
SET @MonName = 'Billion ' + @MonName
SELECT @MonName = numname + ' ' + @MonName FROM NumNameTbl WHERE val = (CASE LEFT(RIGHT(@TempMon,2),1) WHEN 1 THEN RIGHT(@TempMon,2) ELSE RIGHT(@TempMon,1) END)
IF LEN(@TempMon) > 1
BEGIN
IF LEFT(RIGHT(@TempMon,2),1) NOT IN (0,1)
SELECT @MonName = numname + (CASE RIGHT(@TempMon,1) WHEN 0 THEN ' ' ELSE '-' END) + @MonName FROM NumNameTbl WHERE val = CAST((LEFT(RIGHT(@TempMon,2),1) + '0') AS int)
END
IF LEN(@TempMon) = 3 AND CAST(LEFT(@TempMon,1) AS INT) != 0
SELECT @MonName = numname + ' Hundred ' + @MonName FROM NumNameTbl WHERE val = CAST(LEFT(@TempMon,1) AS INT)
END
END
IF CHARINDEX('One',@MonName) = 1 AND CHARINDEX('Dollars',@MonName) = 5
SET @MonName = REPLACE(@MonName,'Dollars','Dollar')
RETURN(@MonName)
END
GO
SELECT dbo.MoneyName(2051.56) MoneyText
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply