How to convert the Number into words using Sql Query?

In this code snippets, i have created the stored procedure for how to convert the numbers into words using sql server. The following sql query/stored procedure is used to convert the Number into words.For example if you give the Input number "2010" it will returns the Ouput as "TWO THOUSAND TEN".

The following sql query/stored procedure is used to convert the Number into words. For example if you give the Input number "2010" it will returns the Ouput as "TWO THOUSAND TEN".

``Create Procedure NumberToWord  @Number AS BIGINT  AS  BEGIN  DECLARE @Digits INT  SET @Digits = @Number  IF (LEN(@Digits) > 4)   BEGIN  RAISERROR ('PLEASE ENTER WITH IN 4 DIGIT NUMBERS',12,-1)  END  ELSE IF (LEN(@Digits) <= 4)   BEGIN  DECLARE @NumWords TABLE(Number INT, Ones VARCHAR(15),  Tens VARCHAR(15), Hundreds VARCHAR(15), Thousands VARCHAR(15))  INSERT INTO @NumWords  SELECT 0,'','','','' UNION ALL   SELECT 1,'ONE','TEN','ONE HUNDRED','ONE THOUSAND' UNION ALL   SELECT 2,'TWO','TWENTY','TWO HUNDRED','TWO THOUSAND' UNION ALL   SELECT 3,'THREE','THIRTY','THREE HUNDRED','THREE THOUSAND' UNION ALL   SELECT 4,'FOUR','FORTY','FOUR HUNDRED','FOUR THOUSAND'  UNION ALL   SELECT 5,'FIVE','FIFTY','FIVE HUNDRED','FIVE THOUSAND'  UNION ALL   SELECT 6,'SIX','SIXTY','SIX HUNDRED','SIX THOUSAND'  UNION ALL   SELECT 7,'SEVEN','SEVENTY','SEVEN HUNDRED','SEVEN THOUSAND'  UNION ALL   SELECT 8,'EIGHT','EIGHTY','EIGHT HUNDRED','EIGHT THOUSAND' UNION ALL   SELECT 9,'NINE','NINETY','NINE HUNDRED','NINETHOUSAND' UNION ALL   SELECT 10,'NINETEEN','','','' 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','','',''       SELECT RTRIM(MAX(CASE WHEN NUMBER = SUBSTRING(REVERSE(@Digits),4,1) THEN THOUSANDS ELSE '' END)) --FROM @NumWords   		+SPACE(2)+ LTRIM(MAX(CASE WHEN NUMBER = SUBSTRING(REVERSE(@Digits),3,1) THEN HUNDREDS ELSE '' END) )          +SPACE(2)+ MAX(CASE WHEN RIGHT(@Digits,2) NOT BETWEEN 11 AND 19 AND NUMBER = SUBSTRING(REVERSE(@Digits),2,1) THEN TENS ELSE '' END)          +SPACE(2)+ MAX(CASE WHEN RIGHT(@Digits,2) NOT BETWEEN 11 AND 19 AND NUMBER = RIGHT(@Digits,1) THEN ONES          WHEN RIGHT(@Digits,2) BETWEEN 11 AND 19 AND NUMBER = RIGHT(@Digits,2) THEN ONES ELSE '' END)          As "Number to Words"FROM @NUMWORDS   END  END  --EXEC NumberToWord 2010``

Author: Rakesh07 Jan 2011 Member Level: Bronze   Points : 1

Hi,
I found how to convert number into word without using sql on dotnetcodes and it really good article
Visit:
www.dotnetcodes.com/dotnetcodes/code/Source-Codes-15-How-To-convert-a-Number--into-Word.aspx

Author: Ravindran14 Jan 2011 Member Level: Diamond   Points : 0

hi,

Nice code i execute this ...it working nicely

Guest Author: Raj07 May 2012

i wnat the procedure for 15 digits atleast please help me if any one have

• Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
• No HTML formatting and links to other web sites are allowed.
• This is a strictly moderated site. Absolutely no spam allowed.
• Name:
Email: