# number to words

• ### Question

• Hi,

Is there any Function/SP to convert number to words.

If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVR
Tuesday, May 31, 2011 12:37 PM

• There is no builtin sp/function

May be this will help ...

http://www.dbforums.com/microsoft-sql-server/1213334-how-convert-numbers-into-words.html

Code is from this link .. you can convert it to Storedprocedure if you like ....

```DECLARE @i int, @temp char(1), @s VARCHAR(20), @result VARCHAR(255),@N BIGINT /*INPUT */
Set @N=123456789
SELECT @s=convert(varchar(20), @n)
SELECT @i=LEN(@s)
SELECT @result=''
WHILE (@i>0)
BEGIN
SELECT @temp=(SUBSTRING(@s,@i,1))
IF ((LEN(@s)-@i) % 3)=1
IF @temp='1'
SELECT @result=CASE (SUBSTRING(@s,@i+1,1))
WHEN '0' THEN 'ten'
WHEN '1' THEN 'eleven'
WHEN '2' THEN 'twelve'
WHEN '3' THEN 'thirteen'
WHEN '4' THEN 'fourteen'
WHEN '5' THEN 'fifteen'
WHEN '6' THEN 'sixteen'
WHEN '7' THEN 'seventeen'
WHEN '8' THEN 'eighteen'
WHEN '9' THEN 'nineteen'
END+' '+CASE
WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
WHEN ((LEN(@s)-@i)=7) THEN 'million '
WHEN ((LEN(@s)-@i)=10) THEN 'billion '
WHEN ((LEN(@s)-@i)=13) THEN 'trillion '
ELSE ''
END+@result
ELSE
BEGIN
SELECT @result=CASE (SUBSTRING(@s,@i+1,1))
WHEN '0' THEN ''
WHEN '1' THEN 'one'
WHEN '2' THEN 'two'
WHEN '3' THEN 'three'
WHEN '4' THEN 'four'
WHEN '5' THEN 'five'
WHEN '6' THEN 'six'
WHEN '7' THEN 'seven'
WHEN '8' THEN 'eight'
WHEN '9' THEN 'nine'
END+' '+ CASE
WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
WHEN ((LEN(@s)-@i)=7) THEN 'million '
WHEN ((LEN(@s)-@i)=10) THEN 'billion '
WHEN ((LEN(@s)-@i)=13) THEN 'trillion '
ELSE ''
END+@result
SELECT @result=CASE @temp
WHEN '0' THEN ''
WHEN '1' THEN 'ten'
WHEN '2' THEN 'twenty'
WHEN '3' THEN 'thirty'
WHEN '4' THEN 'fourty'
WHEN '5' THEN 'fifty'
WHEN '6' THEN 'sixty'
WHEN '7' THEN 'seventy'
WHEN '8' THEN 'eighty'
WHEN '9' THEN 'ninety'
END+' '+@result
END
IF (((LEN(@s)-@i) % 3)=2) OR (((LEN(@s)-@i) % 3)=0) AND (@i=1)
BEGIN
SELECT @result=CASE @temp
WHEN '0' THEN ''
WHEN '1' THEN 'one'
WHEN '2' THEN 'two'
WHEN '3' THEN 'three'
WHEN '4' THEN 'four'
WHEN '5' THEN 'five'
WHEN '6' THEN 'six'
WHEN '7' THEN 'seven'
WHEN '8' THEN 'eight'
WHEN '9' THEN 'nine'
END +' '+CASE
WHEN (@s='0') THEN 'zero'
WHEN (@temp<>'0')AND( ((LEN(@s)-@i) % 3)=2) THEN 'hundred '
ELSE ''
END + CASE
WHEN ((LEN(@s)-@i)=3) THEN 'thousand '
WHEN ((LEN(@s)-@i)=6) THEN 'million '
WHEN ((LEN(@s)-@i)=9) THEN 'billion '
WHEN ((LEN(@s)-@i)=12) THEN 'trillion '
ELSE ''
END+ @result
END
SELECT @i=@i-1
END
select REPLACE(@result,' ',' ')
```

• Proposed as answer by Tuesday, May 31, 2011 1:28 PM
• Marked as answer by Thursday, June 2, 2011 6:30 AM
Tuesday, May 31, 2011 12:49 PM
• Hi

There is no special function to convert number into words but  in oracle is possible

in SQL you have to hard code in SP

like that

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

the above SP taken from     http://www.sqlusa.com/bestpractices2008/number-to-words

If u find any thing please posted..

thanks

nathan

• Proposed as answer by Tuesday, May 31, 2011 1:28 PM
• Marked as answer by Thursday, June 2, 2011 6:30 AM
Tuesday, May 31, 2011 1:14 PM

### All replies

• There is no builtin sp/function

May be this will help ...

http://www.dbforums.com/microsoft-sql-server/1213334-how-convert-numbers-into-words.html

Code is from this link .. you can convert it to Storedprocedure if you like ....

```DECLARE @i int, @temp char(1), @s VARCHAR(20), @result VARCHAR(255),@N BIGINT /*INPUT */
Set @N=123456789
SELECT @s=convert(varchar(20), @n)
SELECT @i=LEN(@s)
SELECT @result=''
WHILE (@i>0)
BEGIN
SELECT @temp=(SUBSTRING(@s,@i,1))
IF ((LEN(@s)-@i) % 3)=1
IF @temp='1'
SELECT @result=CASE (SUBSTRING(@s,@i+1,1))
WHEN '0' THEN 'ten'
WHEN '1' THEN 'eleven'
WHEN '2' THEN 'twelve'
WHEN '3' THEN 'thirteen'
WHEN '4' THEN 'fourteen'
WHEN '5' THEN 'fifteen'
WHEN '6' THEN 'sixteen'
WHEN '7' THEN 'seventeen'
WHEN '8' THEN 'eighteen'
WHEN '9' THEN 'nineteen'
END+' '+CASE
WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
WHEN ((LEN(@s)-@i)=7) THEN 'million '
WHEN ((LEN(@s)-@i)=10) THEN 'billion '
WHEN ((LEN(@s)-@i)=13) THEN 'trillion '
ELSE ''
END+@result
ELSE
BEGIN
SELECT @result=CASE (SUBSTRING(@s,@i+1,1))
WHEN '0' THEN ''
WHEN '1' THEN 'one'
WHEN '2' THEN 'two'
WHEN '3' THEN 'three'
WHEN '4' THEN 'four'
WHEN '5' THEN 'five'
WHEN '6' THEN 'six'
WHEN '7' THEN 'seven'
WHEN '8' THEN 'eight'
WHEN '9' THEN 'nine'
END+' '+ CASE
WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
WHEN ((LEN(@s)-@i)=7) THEN 'million '
WHEN ((LEN(@s)-@i)=10) THEN 'billion '
WHEN ((LEN(@s)-@i)=13) THEN 'trillion '
ELSE ''
END+@result
SELECT @result=CASE @temp
WHEN '0' THEN ''
WHEN '1' THEN 'ten'
WHEN '2' THEN 'twenty'
WHEN '3' THEN 'thirty'
WHEN '4' THEN 'fourty'
WHEN '5' THEN 'fifty'
WHEN '6' THEN 'sixty'
WHEN '7' THEN 'seventy'
WHEN '8' THEN 'eighty'
WHEN '9' THEN 'ninety'
END+' '+@result
END
IF (((LEN(@s)-@i) % 3)=2) OR (((LEN(@s)-@i) % 3)=0) AND (@i=1)
BEGIN
SELECT @result=CASE @temp
WHEN '0' THEN ''
WHEN '1' THEN 'one'
WHEN '2' THEN 'two'
WHEN '3' THEN 'three'
WHEN '4' THEN 'four'
WHEN '5' THEN 'five'
WHEN '6' THEN 'six'
WHEN '7' THEN 'seven'
WHEN '8' THEN 'eight'
WHEN '9' THEN 'nine'
END +' '+CASE
WHEN (@s='0') THEN 'zero'
WHEN (@temp<>'0')AND( ((LEN(@s)-@i) % 3)=2) THEN 'hundred '
ELSE ''
END + CASE
WHEN ((LEN(@s)-@i)=3) THEN 'thousand '
WHEN ((LEN(@s)-@i)=6) THEN 'million '
WHEN ((LEN(@s)-@i)=9) THEN 'billion '
WHEN ((LEN(@s)-@i)=12) THEN 'trillion '
ELSE ''
END+ @result
END
SELECT @i=@i-1
END
select REPLACE(@result,' ',' ')
```

• Proposed as answer by Tuesday, May 31, 2011 1:28 PM
• Marked as answer by Thursday, June 2, 2011 6:30 AM
Tuesday, May 31, 2011 12:49 PM
• Hi

There is no special function to convert number into words but  in oracle is possible

in SQL you have to hard code in SP

like that

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

the above SP taken from     http://www.sqlusa.com/bestpractices2008/number-to-words

If u find any thing please posted..

thanks

nathan

• Proposed as answer by Tuesday, May 31, 2011 1:28 PM
• Marked as answer by Thursday, June 2, 2011 6:30 AM
Tuesday, May 31, 2011 1:14 PM
• Hi,

Is there any Function/SP to convert number to words.

If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVR
http://yogeshyrbyogi.blogspot.com/2011/04/user-defined-function-to-convert-number.html

My Blog
Tuesday, May 31, 2011 1:20 PM
• To convert a number into English words upto the length of 60 follow :

To convert a number into Hindi words upto the length of 40 follow :

Thursday, August 30, 2012 10:59 AM
• This does not work for one million:

Monday, August 6, 2018 12:43 PM
• A method for converting numbers into words using only standard SQL by Stu Bloom follows. This was posted on 2002 Jan 02 on the SQL Server Programming newsgroup.

First, create a table

CREATE TABLE NbrWords
(number INTEGER PRIMARY KEY,
word VARCHAR(30) NOT NULL);

Then populate it with the literal strings of all NbrWords from 0 to 999. Assuming that your range is 1 - 999,999,999 use the following query; it should be obvious how to extend it for larger numbers and fractional parts.

CASE WHEN :num < 1000
THEN (SELECT word FROM NbrWords
WHERE number = :num)
WHEN :num < 1000000
THEN (SELECT word FROM NbrWords
WHERE number = :num / 1000)
|| ' thousand '
|| (SELECT word FROM NbrWords
WHERE MOD (number = :num, 1000))
WHEN :num < 1000000000
THEN (SELECT word FROM NbrWords
WHERE number = :num / 1000000)
|| ' million '
|| (SELECT word FROM NbrWords
WHERE number = MOD((:num / 1000), 1000))
|| CASE WHEN MOD((:num / 1000), 1000) > 0
THEN ' thousand '
ELSE '' END
|| (SELECT word FROM NbrWords
WHERE number = MOD(:num, 1000))

END;

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Tuesday, August 7, 2018 12:30 AM