locked
number to words RRS feed

  • 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

Answers

  • 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....
    • Proposed as answer by RaheelKhan Tuesday, May 31, 2011 1:28 PM
    • Marked as answer by DVR Prasad 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 RaheelKhan Tuesday, May 31, 2011 1:28 PM
    • Marked as answer by DVR Prasad 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 '
    					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....
    • Proposed as answer by RaheelKhan Tuesday, May 31, 2011 1:28 PM
    • Marked as answer by DVR Prasad 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 RaheelKhan Tuesday, May 31, 2011 1:28 PM
    • Marked as answer by DVR Prasad 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

    Yogesh Bhadauriya
    My Blog
    Tuesday, May 31, 2011 1:20 PM
  • 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

    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