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

Answer 1

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 '
					WHEN ((LEN(@s)-@i)=16) THEN 'quadrillion '
					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 '
					WHEN ((LEN(@s)-@i)=16) THEN 'quadrillion '
					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 '
				WHEN ((LEN(@s)-@i)=15) THEN 'quadrillion '
				ELSE ''
					END+ @result
		END
		SELECT @i=@i-1
	END
	select REPLACE(@result,' ',' ')

---

If this answer is helpful to you .. Please mark as Answer....

Answer 2

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

 

Answer 3

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

---

Yogesh Bhadauriya
My Blog

Answer 4

To convert a number into English words upto the length of 60 follow :

http://govindbadkur.blogspot.in/2012/08/convert-number-into-words.html

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

http://govindbadkur.blogspot.in/2012/08/convert-number-into-hindi-words_8.html

Answer 5

This does not work for one million:

Answer 6

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