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


Comments

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: