DotnetSlackers Code-Snippet

MaximumASP
Social Bookmarking
Online Shopping
asp.net hosting
UK online local dating

Converting Numbers to Word in sql server

Published: 20 Nov 2009 By Gulab Chand Tejwani

This sql function tells you hot to convert numbers to words using T-sql. dbo.NUMBERTOWORDS('2345') will return to you as Two Thousand Three Hundread Fourty Five.

-- Author  :- Gulab Chand Tejwani		
-- Date :-  20th Nov 2009

CREATE function NUMBERTOWORDS(@n bigint ) 
--Returns the number as words.
returns VARCHAR(255) 
as
BEGIN
DECLARE @i int, @temp char(1),  @s VARCHAR(20), @result VARCHAR(255)
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 '
			WHEN ((LEN(@s)-@i)=19) THEN 'Quintillion '
			WHEN ((LEN(@s)-@i)=22) THEN 'Sextillion '
			WHEN ((LEN(@s)-@i)=25) THEN 'Septillion '
			WHEN ((LEN(@s)-@i)=28) THEN 'Octillion '
			WHEN ((LEN(@s)-@i)=31) THEN 'Nonillion '
			WHEN ((LEN(@s)-@i)=34) THEN 'Decillion '
			WHEN ((LEN(@s)-@i)=37) THEN 'Undecillion '
			WHEN ((LEN(@s)-@i)=40) THEN 'Duodecillion '
			WHEN ((LEN(@s)-@i)=43) THEN 'Tredecillion '
			WHEN ((LEN(@s)-@i)=46) THEN 'Quattuordecillion '
			WHEN ((LEN(@s)-@i)=49) THEN 'Quindecillion '
			WHEN ((LEN(@s)-@i)=52) THEN 'Sexdecillion '
			WHEN ((LEN(@s)-@i)=55) THEN 'Septendecillion '
			WHEN ((LEN(@s)-@i)=58) THEN 'Octodecillion '
			WHEN ((LEN(@s)-@i)=61) THEN 'Novemdecillion '
			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 'Tthousand '
			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 '
			WHEN ((LEN(@s)-@i)=19) THEN 'Quintillion '
			WHEN ((LEN(@s)-@i)=22) THEN 'Sextillion '
			WHEN ((LEN(@s)-@i)=25) THEN 'Septillion '
			WHEN ((LEN(@s)-@i)=28) THEN 'Octillion '
			WHEN ((LEN(@s)-@i)=31) THEN 'Nonillion '
			WHEN ((LEN(@s)-@i)=34) THEN 'Decillion '
			WHEN ((LEN(@s)-@i)=37) THEN 'Undecillion '
			WHEN ((LEN(@s)-@i)=40) THEN 'Duodecillion '
			WHEN ((LEN(@s)-@i)=43) THEN 'Tredecillion '
			WHEN ((LEN(@s)-@i)=46) THEN 'Quattuordecillion '
			WHEN ((LEN(@s)-@i)=49) THEN 'Quindecillion '
			WHEN ((LEN(@s)-@i)=52) THEN 'Sexdecillion '
			WHEN ((LEN(@s)-@i)=55) THEN 'Septendecillion '
			WHEN ((LEN(@s)-@i)=58) THEN 'Octodecillion '
			WHEN ((LEN(@s)-@i)=61) THEN 'Novemdecillion '
			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 '
		WHEN ((LEN(@s)-@i)=18) THEN 'Quintillion '
		WHEN ((LEN(@s)-@i)=21) THEN 'Sextillion '
		WHEN ((LEN(@s)-@i)=24) THEN 'Septillion '
		WHEN ((LEN(@s)-@i)=27) THEN 'Octillion '
		WHEN ((LEN(@s)-@i)=30) THEN 'Nonillion '
		WHEN ((LEN(@s)-@i)=33) THEN 'Decillion '
		WHEN ((LEN(@s)-@i)=36) THEN 'Undecillion '
		WHEN ((LEN(@s)-@i)=39) THEN 'Duodecillion '
		WHEN ((LEN(@s)-@i)=42) THEN 'Tredecillion '
		WHEN ((LEN(@s)-@i)=45) THEN 'Quattuordecillion '
		WHEN ((LEN(@s)-@i)=48) THEN 'Quindecillion '
		WHEN ((LEN(@s)-@i)=51) THEN 'Sexdecillion '
		WHEN ((LEN(@s)-@i)=54) THEN 'Septendecillion '
		WHEN ((LEN(@s)-@i)=57) THEN 'Octodecillion '
		WHEN ((LEN(@s)-@i)=60) THEN 'Novemdecillion '
		ELSE ''
			END+ @result
END
SELECT @i=@i-1
END
return REPLACE(@result,'  ',' ')
END

sql function

Please login to leave a comment.

Login

Lost Password?

Navigation

Our Projects

Email Service

News Archive

Subscribe

Product Spotlight

Hosting

Converting Numbers to Word in sql server