October 11, 2008 at 5:14 am
Dear All,
Is ther any in build function to convert numeric into words.?
I mean I want to display AMOUNT in WORDS.
Thanks,
Santhosh Nair.
October 12, 2008 at 9:32 am
Sure... here's one I was "playing" with and never did go back an finish... It stinks because of all the correlated subqueries... probably gonna be terrible for performance.
DECLARE @SomeNumber VARCHAR(25)
CREATE TABLE #NumberNames (Number BIGINT PRIMARY KEY CLUSTERED,Name VARCHAR(12))
INSERT INTO #NumberNames (Number,Name)
SELECT 0,' Zero' UNION ALL
SELECT 1,' One' UNION ALL
SELECT 2,' Two' UNION ALL
SELECT 3,' Three' UNION ALL
SELECT 4,' Four' UNION ALL
SELECT 5,' Five' UNION ALL
SELECT 6,' Six' UNION ALL
SELECT 7,' Seven' UNION ALL
SELECT 8,' Eight' UNION ALL
SELECT 9,' 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' UNION ALL
SELECT 100,' Hundred' UNION ALL
SELECT 1000,' Thousand' UNION ALL
SELECT POWER(10.0,6),' Million' UNION ALL
SELECT POWER(10.0,9),' Billion' UNION ALL
SELECT POWER(10.0,12),' Trillion' UNION ALL
SELECT POWER(10.0,15),' Quadrillion' UNION ALL
SELECT POWER(10.0,18),' Quintillion'
DECLARE @SomeNumber VARCHAR(25)
SET @SomeNumber = 1234567891
DECLARE @Return VARCHAR(500)
SET @RETURN =''
SELECT --d.Triplet,d.ThousandsBreak,
@Return = @Return +
CASE
WHEN LEFT(d.Triplet,1)>0
THEN (SELECT Name FROM #NumberNames WHERE Number = LEFT(d.Triplet,1)) + ' Hundred'
ELSE ''
END
+ CASE
WHEN RIGHT(d.Triplet,2)>19
THEN (SELECT Name FROM #NumberNames WHERE Number = SUBSTRING(d.Triplet,2,1)*10)
+ (SELECT ISNULL(NULLIF(Name,' Zero'),'') FROM #NumberNames WHERE Number = RIGHT(d.Triplet,1))
WHEN RIGHT(d.Triplet,2) BETWEEN 1 AND 19
THEN (SELECT Name FROM #NumberNames WHERE Number = RIGHT(d.Triplet,2))
WHEN @SomeNumber = 0
THEN ' Zero'
ELSE ''
END
+ d.ThousandsName
FROM
(
SELECT t.N,
REPLACE(STR(REVERSE(SUBSTRING(REVERSE(@SomeNumber),t.N,3)),3),' ','0') AS Triplet,
POWER(10.0,t.N-1) AS ThousandsBreak,
ISNULL(NULLIF(Names.Name,' One'),'') AS ThousandsName
FROM dbo.Tally t
INNER JOIN #NumberNames Names
ON POWER(10.0,t.N-1) = Names.Number
WHERE t.N<=LEN(@SomeNumber)
AND t.N%3-1=0
)d
ORDER BY d.N DESC
PRINT LTRIM(@Return)
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2008 at 9:34 am
Here's a prettied-up version, commas, hyphenation and casing:
--============================================================================================
-- Create and populate a Tally table
--By Jeff Moden, 2008/05/07 http://www.sqlservercentral.com/articles/TSQL/62867/
--============================================================================================
--===== Conditionally drop and create the table/Primary Key
IF OBJECT_ID('Tempdb..#Tally') IS NOT NULL
BEGIN
DROP TABLE #Tally
END
--===== Conditionally drop and create the table/Primary Key
IF OBJECT_ID('Tempdb..#Tally') IS NULL
BEGIN
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
END
--===== Add a Primary Key to maximize performance
IF OBJECT_ID('Tempdb..PK_#Tally') IS NULL
BEGIN
ALTER TABLE #Tally
ADD CONSTRAINT PK_#Tally_N
PRIMARY KEY CLUSTERED (N)
WITH FILLFACTOR = 100
END
--===========================================END TALLY TABLE SETUP========================================
DECLARE @SomeNumber VARCHAR(25)
IF object_id('Tempdb..#NumberNames') IS NOT NULL BEGIN DROP TABLE #NumberNames END
CREATE TABLE #NumberNames (Number BIGINT PRIMARY KEY CLUSTERED,Name VARCHAR(12))
INSERT INTO #NumberNames (Number,Name)
SELECT 0,' Zero' UNION ALL
SELECT 1,' One' UNION ALL
SELECT 2,' Two' UNION ALL
SELECT 3,' Three' UNION ALL
SELECT 4,' Four' UNION ALL
SELECT 5,' Five' UNION ALL
SELECT 6,' Six' UNION ALL
SELECT 7,' Seven' UNION ALL
SELECT 8,' Eight' UNION ALL
SELECT 9,' 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' UNION ALL
SELECT 100,' Hundred' UNION ALL
SELECT 1000,' Thousand' UNION ALL
SELECT POWER(10.0,6),' Million' UNION ALL
SELECT POWER(10.0,9),' Billion' UNION ALL
SELECT POWER(10.0,12),' Trillion' UNION ALL
SELECT POWER(10.0,15),' Quadrillion' UNION ALL
SELECT POWER(10.0,18),' Quintillion'
--DECLARE @SomeNumber VARCHAR(25)
SET @SomeNumber = 1000000003
DECLARE @Return VARCHAR(500)
SET @RETURN =''
SELECT --d.Triplet,d.ThousandsBreak,
@Return = @Return +
CASE
WHEN LEFT(d.Triplet,1)>0
THEN (SELECT Name FROM #NumberNames WHERE Number = LEFT(d.Triplet,1)) + ' Hundred'
ELSE ''
END
+ CASE
WHEN RIGHT(d.Triplet,2)>19 AND RIGHT(d.Triplet,1) BETWEEN 1 AND 9
THEN (SELECT Name FROM #NumberNames WHERE Number = SUBSTRING(d.Triplet,2,1)*10)
+ '-'
+ (SELECT ISNULL(NULLIF(ltrim(Name),' Zero'),'') FROM #NumberNames WHERE Number = RIGHT(d.Triplet,1))
WHEN RIGHT(d.Triplet,2)>19
THEN (SELECT Name FROM #NumberNames WHERE Number = SUBSTRING(d.Triplet,2,1)*10)
+ (SELECT ISNULL(NULLIF(Name,' Zero'),'') FROM #NumberNames WHERE Number = RIGHT(d.Triplet,1))
WHEN RIGHT(d.Triplet,2) BETWEEN 1 AND 19
THEN (SELECT Name FROM #NumberNames WHERE Number = RIGHT(d.Triplet,2))
WHEN @SomeNumber = 0
THEN ' Zero'
ELSE ''
END
+ CASE
WHEN LEFT(d.Triplet,3)>0 AND LEFT(d.Triplet,2)=0
THEN d.ThousandsName + ','
WHEN LEFT(d.Triplet,3)>0 AND LEFT(d.Triplet,1)=0 AND LEFT(d.Triplet,2)=0
THEN d.ThousandsName
WHEN LEFT(d.Triplet,3)>0
THEN d.ThousandsName +','
ELSE ''
END
FROM
(
SELECT t.N,
REPLACE(STR(REVERSE(SUBSTRING(REVERSE(@SomeNumber),t.N,3)),3),' ','0') AS Triplet,
POWER(10.0,t.N-1) AS ThousandsBreak,
ISNULL(NULLIF(Names.Name,' One'),'') AS ThousandsName
FROM dbo.#Tally t
INNER JOIN #NumberNames Names
ON POWER(10.0,t.N-1) = Names.Number
WHERE t.N<=LEN(@SomeNumber)
AND t.N%3-1=0
)d
ORDER BY d.N DESC
SELECT CASE
WHEN right(rtrim(@Return),1) = ','
THEN Upper(left(LTRIM(@Return),1))+lower(substring(ltrim(@Return),2,len(rtrim(ltrim(@Return)))-2))
ELSE Upper(left(LTRIM(@Return),1))+lower(substring(ltrim(@Return),2,len(rtrim(ltrim(@Return)))-1))
END
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 14, 2008 at 7:30 am
That site has a lot of really great stuff. Aaron Bertrand, IIRC.
And I too think this is a presentation layer item. Or perhaps a CLR function??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 14, 2008 at 7:21 pm
TheSQLGuru (10/14/2008)
That site has a lot of really great stuff. Aaron Bertrand, IIRC.And I too think this is a presentation layer item. Or perhaps a CLR function??
I agree... well, except for the CLR thingy 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2008 at 4:19 am
nairsanthu1977 (10/11/2008)
Dear All,Is ther any in build function to convert numeric into words.?
I mean I want to display AMOUNT in WORDS.
Thanks,
Santhosh Nair.
Where do you want to show data?
If you use Reports, make use of TOWORDS function (Crystal Reports)
Failing to plan is Planning to fail
October 15, 2008 at 7:16 am
Jeff Moden (10/14/2008)
TheSQLGuru (10/14/2008)
That site has a lot of really great stuff. Aaron Bertrand, IIRC.And I too think this is a presentation layer item. Or perhaps a CLR function??
I agree... well, except for the CLR thingy 😛
HAH! I have been paid a decent amount of money this year cleaning up CLR-related performance issues, so I am with you in general about CLR being 'bad'. But there truly are reasons for using it - just like any other tool. I feel that this could be one of those, at least for report queries where you are not sending the results back to some front-end system that can handle the OP's need.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 15, 2008 at 8:14 am
Yep... I agree, Kevin... especially if you have a function like "ToWords". That and RegEx Replace seem to be pretty good.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2008 at 10:00 am
Here's another way, similar. Quite quick.
[font="Courier New"]ALTER FUNCTION dbo.ToWords(@TheNumber DECIMAL (15,2), @CurrencyName VARCHAR(20), @JoinWord VARCHAR(20), @CentsName VARCHAR(20))
RETURNS VARCHAR(200)
-- USAGE: SELECT dbo.ToWords(1.01, 'dollar(s)', 'and', 'cent(s)')
-- MAX. VALUE: 999999999999.99 (twelve nines before the decimal point)
BEGIN
DECLARE @numberstr CHAR (15), @NewString VARCHAR(200)
SET @numberstr = STR(@TheNumber, 15, 2)
SET @NewString = ''
DECLARE @tblNum TABLE(Num BIGint, MaxNum BIGint, NumStr VARCHAR(20))
INSERT INTO @tblNum
SELECT 1, 1, 'one' UNION SELECT 2, 2, 'two' UNION
SELECT 3, 3, 'three' UNION SELECT 4, 4, 'four' UNION
SELECT 5, 5, 'five' UNION SELECT 6, 6, 'six' UNION
SELECT 7, 7, 'seven' UNION SELECT 8, 8, 'eight' UNION
SELECT 9, 9, 'nine' UNION SELECT 10, 10, 'ten' UNION
SELECT 11, 11, 'eleven' UNION SELECT 12, 12, 'twelve' UNION
SELECT 13, 13, 'thirteen' UNION SELECT 14, 14, 'fourteen' UNION
SELECT 15, 15, 'fifteen' UNION SELECT 16, 16, 'sixteen' UNION
SELECT 17, 17, 'seventeen' UNION SELECT 18, 18, 'eighteen' UNION
SELECT 19, 19, 'nineteen' UNION SELECT 20, 29, 'twenty' UNION
SELECT 30, 39, 'thirty' UNION SELECT 40, 49, 'forty' UNION
SELECT 50, 59, 'fifty' UNION SELECT 60, 69, 'sixty' UNION
SELECT 70, 79, 'seventy' UNION SELECT 80, 89, 'eighty' UNION
SELECT 90, 99, 'ninety' UNION SELECT 100, 999, 'hundred' UNION
SELECT 1000, 999999, 'thousand' UNION SELECT 1000000, 999999999, 'million' UNION
SELECT 1000000000, 999999999999, 'billion'
SELECT @NewString = @NewString
+ CASE WHEN h.NumStr IS NULL THEN '' ELSE h.NumStr + ' ' + c.NumStr + ' ' END
+ CASE WHEN number < 15 AND h.NumStr IS NOT NULL AND (t.NumStr IS NOT NULL OR u.NumStr IS NOT NULL) THEN @JoinWord + ' ' ELSE '' END
+ CASE WHEN number < 15 AND t.NumStr IS NOT NULL THEN t.NumStr + ' ' ELSE '' END
+ CASE WHEN number < 15 AND u.NumStr IS NOT NULL THEN u.NumStr + ' ' ELSE '' END
+ CASE WHEN number = 12 THEN @CurrencyName + ' ' ELSE '' END
+ CASE WHEN number = 15 AND (t.NumStr IS NOT NULL OR u.NumStr IS NOT NULL) THEN @JoinWord + ' ' ELSE '' END
+ CASE WHEN number = 15 AND t.NumStr IS NOT NULL THEN t.NumStr + ' ' ELSE '' END
+ CASE WHEN number = 15 AND u.NumStr IS NOT NULL THEN u.NumStr + ' ' ELSE '' END
+ CASE WHEN number = 15 AND (t.NumStr IS NOT NULL OR u.NumStr IS NOT NULL) THEN @CentsName + ' ' ELSE '' END
+ CASE WHEN k.NumStr IS NULL THEN '' ELSE k.NumStr + ', ' END
FROM (SELECT 3 AS number UNION ALL SELECT 6 UNION ALL SELECT 9 UNION ALL SELECT 12 UNION ALL SELECT 15) n
LEFT JOIN @tblNum h ON number < 15 AND SUBSTRING(@numberstr, number-2, 1) BETWEEN h.Num AND h.MaxNum
LEFT JOIN @tblNum t ON SUBSTRING(@numberstr, number-1, 2) BETWEEN t.Num AND t.MaxNum AND t.Num > 9
LEFT JOIN @tblNum u ON SUBSTRING(@numberstr, number, 1) BETWEEN u.Num AND u.MaxNum AND NOT SUBSTRING(@numberstr, number-1, 2) BETWEEN 10 AND 19
LEFT JOIN @tblNum c ON number < 15 AND c.Num = 100
LEFT JOIN @tblNum k ON number < 12 AND k.Num = POWER(10, 12-number) AND k.Num < @TheNumber
RETURN @NewString
END
GO
SELECT dbo.ToWords(number, 'dollar(s)', 'and', 'cent(s)')
FROM Numbers
WHERE number < 30000
-- about a minute
SELECT dbo.ToWords(999999999999.99, 'dollar(s)', 'and', 'cent(s)')
-- nine hundred and ninety nine billion, nine hundred and ninety nine million,
-- nine hundred and ninety nine thousand, nine hundred and ninety nine dollar(s) and ninety nine cent(s)
SELECT dbo.ToWords(999999999999.99, 'pounds', 'and', 'pence')
-- nine hundred and ninety nine billion, nine hundred and ninety nine million,
-- nine hundred and ninety nine thousand, nine hundred and ninety nine pounds and ninety nine pence
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 26, 2008 at 11:08 am
That looks pretty slick! A minor nitpick would be to use UNION ALL for populating the table variable. Also, if this were to be used regularly I would put that data into a permanent table similar to Numbers.
A non-minor problem is that I get this error every time I run the simple select against the function:
Msg 8114, Level 16, State 5, Line 28
Error converting data type varchar to bigint.
even using '1' as the number.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 26, 2008 at 2:30 pm
TheSQLGuru (11/26/2008)
That looks pretty slick! A minor nitpick would be to use UNION ALL for populating the table variable. Also, if this were to be used regularly I would put that data into a permanent table similar to Numbers.A non-minor problem is that I get this error every time I run the simple select against the function:
Msg 8114, Level 16, State 5, Line 28
Error converting data type varchar to bigint.
even using '1' as the number.
Yikes! What happens when you pass in 1 as the number instead of '1'? I can't test right now, vpn is down.
UNION ALL shouldn't be necessary because none of the rows are duped.
I'll test it for speed tomorrow using a permanent table. A whole minute for returning 30,000 values is IMHO quite slow.
Thanks for the feedback.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 26, 2008 at 2:32 pm
Chris Morris (11/26/2008)
UNION ALL shouldn't be necessary because none of the rows are duped.I'll test it for speed tomorrow using a permanent table. A whole minute for returning 30,000 values is IMHO quite slow.
I think that was SQLGuru's point: because nothing is duplicated, then you'd be better off using UNION ALL (which doesn't do the DISTINCT check).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 26, 2008 at 2:35 pm
Matt Miller (11/26/2008)
Chris Morris (11/26/2008)
UNION ALL shouldn't be necessary because none of the rows are duped.I'll test it for speed tomorrow using a permanent table. A whole minute for returning 30,000 values is IMHO quite slow.
I think that was SQLGuru's point: because nothing is duplicated, then you'd be better off using UNION ALL (which doesn't do the DISTINCT check).
D'oh, thanks Matt. It's getting late here.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 26, 2008 at 3:49 pm
I tried the following calls, all with same error:
SELECT dbo.MoneyToWords(99999999.99, 'dollar(s)', 'and', 'cent(s)')
SELECT dbo.MoneyToWords(1, 'dollar(s)', 'and', 'cent(s)')
SELECT dbo.MoneyToWords('9.99', 'dollar(s)', 'and', 'cent(s)')
SELECT dbo.MoneyToWords('1.10', 'dollar(s)', 'and', 'cent(s)')
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply