Important alert: (current site time 11/28/2014 1:48:18 PM EDT)
 

VB icon

A Number To Words function

Email
Submitted on: 4/4/2008 10:00:44 AM
By: Leon Tayson  
Level: Intermediate
User Rating: By 7 Users
Compatibility: SQL Server 2000, SQL Server 7.0
Views: 47500
author picture
(About the author)
 
     Converts a numeric value to words, i.e, 123 will return ONE HUNDRED TWENTY THREE

 
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
 
Terms of Agreement:   
By using this code, you agree to the following terms...   
  1. You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this code from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
				
--**************************************
-- Name: A Number To Words function
-- Description:Converts a numeric value to words, i.e, 123 will return ONE HUNDRED TWENTY THREE
-- By: Leon Tayson
--
-- Inputs:@num - a numeric value
--
-- Returns:the number in words
--
-- Assumes:Copy and paste the code into Query Analyzer and hit F5, then you can use it just as you would use other SQL functions.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1064&lngWId=5--for details.--**************************************

--**************************************
-- 
-- Name: A Number To Words function
-- Description:Converts a numeric value 
-- to words, i.e, 123 will return ONE HUNDR
-- ED TWENTY THREE
-- By: Leon Tayson
--
-- Inputs:@num - a numeric value
--
-- Returns:the number in words
--
-- Assumes:Copy and paste the code into 
-- Query Analyzer and hit F5, then you can 
-- use it just as you would use other SQL f
-- unctions.
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/vb/scripts/Sh
-- owCode.asp?txtCodeId=1064&lngWId=5--for details.--**************************************
-- 
/***************************************************
Leon C. Tayson
Converts a numeric value TO word
Sample usage:
SELECT dbo.NumToWords(22222)
****************************************************/
CREATE FUNCTION NumToWords
(@num numeric)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @cNum varchar(20)
SET @cNum = @num
DECLARE @len int
SET @len = LEN(@Num)
DECLARE @place int
SET @place = 0
DECLARE @digit varchar(1), @tens varchar(2)
DECLARE @res varchar(1000)
SET @res = ''
DECLARE @tblNum TABLE
(Num int, NumStr varchar(20))
INSERT INTO @tblNum
SELECT 1, ' ONE' UNION SELECT 2, ' TWO' UNION
SELECT 3, ' THREE' UNION SELECT 4, ' FOUR' UNION
SELECT 5, ' FIVE' UNION SELECT 6, ' SIX' UNION
SELECT 7, ' SEVEN' UNION SELECT 8, ' EIGHT' UNION
SELECT 9, ' NINE' UNION SELECT 10, ' TEN' UNION
SELECT 11, ' ELEVEN' UNION SELECT 12, ' TWELVE' UNION
SELECT 13, ' THIRTEEN' UNION SELECT 14, ' FOURTEEN' UNION
SELECT 15, ' FIFTEEN' UNION SELECT 16, ' SIXTEEN' UNION
SELECT 17, ' SEVENTEEN' UNION SELECT 18, ' EIGHTEEN' UNION
SELECT 19, ' NINETEEN' UNION
SELECT 20, ' TWENTY' UNION SELECT 30, ' THIRTY' UNION
SELECT 40, ' FOURTY' UNION SELECT 50, ' FIFTY' UNION
SELECT 60, ' SIXTY' UNION SELECT 70, ' SEVENTY' UNION
SELECT 80, ' EIGHTY' UNION SELECT 90, ' NINETY'
DECLARE @hundred varchar(200)
SET @hundred = ''
	DECLARE @separatorUnit varchar(20)
DECLARE @nStr varchar(20)
WHILE @place < @len
BEGIN
SET @tens = ''
SET @nStr = NULL
SET @digit = SUBSTRING(@cNum, @len - @place, 1)
--SET @digit = SUBSTRING(@Num, @len - @p
-- lace, 1)
		--SET @res = @res + 'd=' + @digit
IF @place % 3 = 0-- one's place
BEGIN
-- Check ten's place
SET @tens = SUBSTRING(@cNum, @len - @place - 1, 2)
--SET @tens = SUBSTRING(@Num, @len - @pl
-- ace - 1, 2)
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @tens
IF @nStr IS NOT NULL
SELECT @hundred = @hundred + @nStr, @place = @place + 1
ELSE
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
SET @hundred = ISNULL(@nStr, '') + @hundred
END
END
ELSE IF @place % 3 = 1-- Ten's place
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit * 10--POWER(10, @place % 3)
SET @hundred = ISNULL(@nStr, '') + @hundred
END
		SET @separatorUnit = 
			CASE @place / 3
				WHEN 0 THEN ''
				WHEN 1 THEN ' THOUSAND'
				WHEN 2 THEN ' MILLION'
				WHEN 3 THEN ' BILLION'
				WHEN 4 THEN ' TRILLION'
				WHEN 5 THEN ' QUADRILLION'
				WHEN 6 THEN ' QUINTILLION'
END --+ @res
IF @place % 3 = 2
		BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
IF @nStr IS NOT NULL
				SET @hundred = @nStr + ' HUNDRED' + @hundred
--print '000 place'
IF LEN(@hundred) > 0
				SET @res = @hundred + @separatorUnit + @res
			SET @hundred = ''
		END
		SET @place = @place + 1
	END
	IF @hundred <> '' SET @res = @hundred + @separatorUnit + @res
	RETURN @res
END


Other 1 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

5/18/2006 10:22:26 PMPating sa Dagat

i tried a five digit number like 12205, output was two hundred five.
(If this comment was disrespectful, please report it.)

 
5/18/2006 10:47:11 PMPating sa Dagat

i tried a 5 digit number like 12205, it gives me "two hundred five" only.
(If this comment was disrespectful, please report it.)

 
9/27/2006 11:17:06 AMDaniel

--the code has some bugs:
SELECT dbo.NumToWords(1111)
--ONE HUNDRED ELEVEN
SELECT dbo.NumToWords(11111)
--ONE HUNDRED ELEVEN

(If this comment was disrespectful, please report it.)

 
2/27/2007 8:00:41 AMronan

there does exist a small bug however it gave me a good start point so thanx!
(If this comment was disrespectful, please report it.)

 
3/24/2007 1:17:16 AMKelvin

there are few bugs in the logic that need to be corrected..i tried with 1001, the output is ONE.. when i tried 1102, it gives me ONE HUNDRED TWO
.. when i tried 1112, it gives me ONE HUNDRED TWELVE

(If this comment was disrespectful, please report it.)

 
4/4/2008 12:28:42 PMLeon Tayson

Thanks to all the feedbacks and my apologies for the late response/fix. Latest code fixes the previously reported issues.

HTH,
(If this comment was disrespectful, please report it.)

 
4/13/2008 8:34:43 PMJeff Moden

What I really, really want to know is... who would use this?
(If this comment was disrespectful, please report it.)

 
7/31/2008 10:31:08 AMJeff Mayer

This code works perfectly. I needed a way to display dollar amounts in text format for legal contract documents. Finding this code saved me weeks of work.
(If this comment was disrespectful, please report it.)

 
2/16/2010 9:13:33 PMMDSoft, Inc.

very similar to this code submitted almost two year earlier. Even the comments looks the same.
(If this comment was disrespectful, please report it.)

 
11/5/2011 11:52:57 AMJon Feucht

You forgot the hyphens!
(If this comment was disrespectful, please report it.)

 
11/5/2011 11:53:58 AMJon Feucht

Great code -- except technically incorrect, since it doesn't include the hyphens!!!
(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.