Converting any number in words
hi try the Below proc.... it gives the output you need....
Procedure :
CREATE FUNCTION fnNumberToWords(@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 @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.fnNumberToWords( @Number % 10)
WHEN @Number BETWEEN 100 AND 999
THEN (dbo.fnNumberToWords( @Number / 100))+' Hundred '+
dbo.fnNumberToWords( @Number % 100)
WHEN @Number BETWEEN 1000 AND 999999
THEN (dbo.fnNumberToWords( @Number / 1000))+' Thousand '+
dbo.fnNumberToWords( @Number % 1000)
WHEN @Number BETWEEN 1000000 AND 999999999
THEN (dbo.fnNumberToWords( @Number / 1000000))+' Million '+
dbo.fnNumberToWords( @Number % 1000000)
WHEN @Number BETWEEN 1000000000 AND 999999999999
THEN (dbo.fnNumberToWords( @Number / 1000000000))+' Billion '+
dbo.fnNumberToWords( @Number % 1000000000)
WHEN @Number BETWEEN 1000000000000 AND 999999999999999
THEN (dbo.fnNumberToWords( @Number / 1000000000000))+' Trillion '+
dbo.fnNumberToWords( @Number % 1000000000000)
WHEN @Number BETWEEN 1000000000000000 AND 999999999999999999
THEN (dbo.fnNumberToWords( @Number / 1000000000000000))+' Quadrillion '+
dbo.fnNumberToWords( @Number % 1000000000000000)
WHEN @Number BETWEEN 1000000000000000000 AND 999999999999999999999
THEN (dbo.fnNumberToWords( @Number / 1000000000000000000))+' Quintillion '+
dbo.fnNumberToWords( @Number % 1000000000000000000)
ELSE ' INVALID INPUT' END
)
SELECT @English = RTRIM(@English)
SELECT @English = RTRIM(LEFT(@English,len(@English)-1))
WHERE RIGHT(@English,1)='-'
RETURN (@English)
END
GO
Test Queries.....
SELECT NumberInEnglish=dbo.fnNumberToWords ( 18)
SELECT NumberInEnglish=dbo.fnNumberToWords ( 67)
SELECT NumberInEnglish=dbo.fnNumberToWords ( 947)
-- Nine Hundred Forty-Seven
SELECT NumberInEnglish=dbo.fnNumberToWords ( 984261)
-- Nine Hundred Eighty-Four Thousand Two Hundred Sixty-One
SELECT NumberInEnglish=dbo.fnNumberToWords ( 777999888)
/* Seven Hundred Seventy-Seven Million Nine Hundred Ninety-Nine Thousand
Eight Hundred Eighty-Eight */
SELECT NumberInEnglish=dbo.fnNumberToWords ( 222777999888)
SELECT NumberInEnglish=dbo.fnNumberToWords ( 555222777999888)
SELECT NumberInEnglish=dbo.fnNumberToWords ( 7446744073709551616)
Source From : http://www.sqlusa.com/bestpractices2008/number-to-words/
Convert number to words - first, second, third and so on
This seems like a simpler approach, a nice recursive algorithm
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
Is there a built-in function that can convert numbers into words in sql server
There is a function, Util_ToWords, in the Free version of the SQL# SQLCLR library that I wrote that does this (well, without the "dollar(s)" and "cents" words added):
SELECT SQL#.Util_ToWords(10); -- Ten
SELECT SQL#.Util_ToWords(100); -- One Hundred
SELECT SQL#.Util_ToWords(1000); -- One Thousand
SELECT SQL#.Util_ToWords(120.20); -- One Hundred Twenty and 20
SELECT SQL#.Util_ToWords(212); -- Two Hundred Twelve
SELECT SQL#.Util_ToWords(123097.4);-- One Hundred Twenty Three Thousand, Ninety Seven and 40
The following example injects the "dollar(s)" and "cents" words into the return value (which was originally intended for printing on checks):
DECLARE @Amount MONEY = 2.08;
;WITH cte AS
(
SELECT N' dollar' + CASE WHEN @Amount >= 1.00 AND @Amount < 2.00 THEN N''
ELSE N's'
END AS [Currency],
SQL#.Util_ToWords(@Amount) AS [Words]
)
SELECT CASE CHARINDEX(N' and ', cte.[Words])
WHEN 0 THEN cte.[Words] + cte.[Currency]
ELSE STUFF(cte.[Words], CHARINDEX(N' and ', cte.[Words]), 0, cte.[Currency])
+ N' cents'
END
FROM cte;
Returns:
Two dollars and 08 cents
how to display number value in words
So how the query works? Well here’s why:
select to_char(to_date(:number,'j'),'jsp') from dual;
If you look into the inner most part of the query to_date(:number,'j') the ‘j’ or J is the Julian Date (January 1, 4713 BC), basically this date is been used for astronomical studies.
So to_date(:number,'j') it take the number represented by number and pretend it is a julian date, convert into a date.
If you pass 3 to number, so it will convert date to 3rd Jan 4713 BC, it means 3 is added to the Julian date.
select to_char(to_date(3,'j'),'jsp') from dual;
Now to_char(to_date(3,'j'),'jsp'), jsp = Now; take that date(to_date(3,'j')) and spell the julian number it represents, the output is:
TO_CH
-----
three
There is a limitation while using Julian dates ,It ranges from 1 to 5373484. That’s why if you put the values after 5373484, it will throw you an error as shown below:
ORA-01854: julian date must be between 1 and 5373484
Hi everyone, it is interesting this topic. I remember when I was learning Oracle in 2005 one of the instructor required me to write a PL/SQL code to convert numbers in words, it was a whole two pages code to reach this.
Here is some reference that could help us to understand the Julian day, that is why we use the letter 'j' or 'J' during this operation.
First there is a website that has the example and explanation about "How To Convert Number Into Words Using Oracle SQL Query":
http://viralpatel.net/blogs/convert-number-into-words-oracle-sql-query/
Second if you want to know more about "Julian day" go to:
http://en.wikipedia.org/wiki/Julian_day
Third if you want to know more about who proposed the Julian day number in 1583, it was by "Joseph Scaliger":
http://en.wikipedia.org/wiki/Joseph_Justus_Scaliger
It is not make sence for me continue repiting what another author in these websites have made, that is why I just posted the link you can access them and read what you need to understand how query like this works:
SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;
//Output: two million four hundred forty-seven thousand eight hundred thirty-four
How to convert number to words - ORACLE
Use the force Luke ;)
SqlFiddleDemo
SELECT UPPER(TO_CHAR(TO_DATE(500,'J'),'Jsp')) || '/=' AS new_value
FROM dual;
The clue is Date in spelled format.
EDIT:
Adding support for negative numbers:
SqlFiddleDemo
WITH cte AS
(
SELECT 10 AS num FROM dual
UNION ALL SELECT -500 FROM dual
UNION ALL SELECT 0 FROM dual
)
SELECT num AS old_value,
decode( sign( num ), -1, 'NEGATIVE ', 0, 'ZERO', NULL ) ||
decode( sign( abs(num) ), +1, to_char( to_date( abs(num),'J'),'JSP') ) || '/=' AS new_value
FROM cte
EDIT 2:##
Adding limited support for float:
SqlFiddleDemo3
WITH cte AS
(
SELECT 10 AS num FROM dual
UNION ALL SELECT -500 FROM dual
UNION ALL SELECT 0 FROM dual
UNION ALL SELECT 10.3 FROM dual
UNION ALL SELECT -10.7 FROM dual
)
SELECT
num AS old_value,
decode( sign( num ), -1, 'NEGATIVE ', 0, 'ZERO', NULL )
|| decode( sign( abs(num) ), +1, to_char( to_date( abs(TRUNC(num)),'J'),'JSP') )
||
CASE
WHEN INSTR (num, '.') > 0
THEN ' POINT ' || TO_CHAR (TO_DATE (TO_NUMBER (SUBSTR(num, INSTR (num, '.') + 1)),'J'),'JSP')
ELSE NULL
END AS new_value
FROM cte
EDIT 3:
for 10.3 output is TEN POINT THREE but it should be TEN POINT THIRTY for 10.3 and TEN POINT THREE for 10.03. How could I achieve this?
Depending of how many digits you want for identity it could be RPADed with 0:
WITH cte AS
(
SELECT 10.03 AS num FROM dual
UNION ALL
SELECT 10.30 FROM dual
UNION ALL
SELECT 10.33 FROM dual
)
SELECT
num AS old_value,
decode( sign( num ), -1, 'NEGATIVE ', 0, 'ZERO', NULL )
|| decode( sign( abs(num) ), +1, to_char( to_date( abs(TRUNC(num)),'J'),'JSP') )
||
CASE
WHEN INSTR (num, '.') > 0
THEN ' POINT ' || TO_CHAR (TO_DATE (TO_NUMBER (RPAD(SUBSTR(num, INSTR (num, '.') + 1)
,2,'0')
),'J'),'JSP')
ELSE NULL
END AS new_value
FROM cte;
db<>fiddle demo
Output:
+-------------+------------------------+
| OLD_VALUE | NEW_VALUE |
+-------------+------------------------+
| 10.03 | TEN POINT THREE |
| 10.3 | TEN POINT THIRTY |
| 10.33 | TEN POINT THIRTY-THREE |
+-------------+------------------------+
Related Topics
Months Between Two Dates in SQL Server with Starting and End Date of Each of Them in SQL Server
Using Oracle Combine Three Tables to One with Pivot
Create a SQL View Based Converting Ranges into Rows
Ora-00600 When Running Alter Command
Cross Table Dependency/Constraint in SQL Database
Determine Contiguous Dates in SQL Gaps and Islands
How to Optimize This SQL Query (Using Indexes)
How to Create Unique Index Where Column Order Is Not Taken into Account (Set)
Multiple Column Values in a Single Row
Insert or Select Strategy to Always Return a Row
When How to Use an Identifier Number Instead of Its Name in Postgresql
Using Table Just After Creating It: Object Does Not Exist
Transpose Rows to Columns Based on Id Column
Order Guarantee for Identity Assignment in Multi-Row Insert in SQL Server