UDF to Convert Number to Words in Indian System

I was in search of a udf which converts number to words in indian numeric sytem, ie. Lakh- crore system. I couldn't find any but an interesting one in the western number system in Novic Software newsletter. I modified it for Indian numeric system. Here it is...



CREATE FUNCTION dbo.udf_Num_ToWords (

@Number Numeric (38, 0) -- Input number with as many as 18 digits

) RETURNS VARCHAR(8000)
/*
* Converts a integer number as large as 34 digits into the
* equivalent words. The first letter is capitalized.
*
* Attribution: Based on NumberToWords by Srinivas Sampath
* as revised by Nick Barclay
* modified by Anuraj Raveendran for Indian Numeric System
*
* Example:
select dbo.udf_Num_ToWords (1234567890) + CHAR(10)
+ dbo.udf_Num_ToWords (0) + CHAR(10)
+ dbo.udf_Num_ToWords (123) + CHAR(10)
select dbo.udf_Num_ToWords(76543210987654321098765432109876543210)

DECLARE @i numeric (38,0)
SET @i = 0
WHILE @I <= 1000 BEGIN
PRINT convert (char(5), @i)
+ convert(varchar(255), dbo.udf_Num_ToWords(@i))
SET @I = @i + 1
END
*
* Published as the T-SQL UDF of the Week Vol 2 #9 2/17/03
****************************************************************/
AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)
DECLARE @Skip INT

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
, @outputString = ''
, @counter = 1
SELECT @length = LEN(@inputNumber)
, @position = LEN(@inputNumber) - 2
, @loops = (LEN(@inputNumber)-1)/2
, @Skip=1

-- make sure there is an extra loop added for the remaining numbers
IF (LEN(@inputNumber)-1) % 2 <> 0 or @loops=0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable SELECT '00', ''
UNION ALL SELECT '01', 'one' UNION ALL SELECT '02', 'two'
UNION ALL SELECT '03', 'three' UNION ALL SELECT '04', 'four'
UNION ALL SELECT '05', 'five' UNION ALL SELECT '06', 'six'
UNION ALL SELECT '07', 'seven' UNION ALL SELECT '08', 'eight'
UNION ALL SELECT '09', 'nine' UNION ALL SELECT '10', 'ten'
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' UNION ALL SELECT '20', 'twenty'
UNION ALL SELECT '30', 'thirty' UNION ALL SELECT '40', 'forty'
UNION ALL SELECT '50', 'fifty' UNION ALL SELECT '60', 'sixty'
UNION ALL SELECT '70', 'seventy' UNION ALL SELECT '80', 'eighty'
UNION ALL SELECT '90', 'ninety'

SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)
WHILE @counter <= @loops BEGIN

-- get chunks of 3 numbers at a time, padded with leading zeros
if @counter>1
set @Skip=0

IF @chunk <> '00' BEGIN
SELECT @tensones = SUBSTRING(@chunk, 1+@Skip, 2)
, @hundreds = SUBSTRING(@chunk, 0+@Skip, 1)
, @tens = SUBSTRING(@chunk, 1+@Skip, 1)
, @ones = SUBSTRING(@chunk, 2+@Skip, 1)

if @counter>1
set @hundreds=0

-- If twenty or less, use the word directly from @NumbersTable
IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE @tensones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' lakh '
WHEN 4 THEN ' Crore ' WHEN 5 THEN ' Arawb '
WHEN 6 THEN ' Kharawb ' WHEN 7 THEN ' Neel '
WHEN 8 THEN ' Padma ' WHEN 9 THEN ' Shankh '
WHEN 10 THEN ' Mahashankh ' WHEN 11 THEN ' Ten Mahashankh '
WHEN 12 THEN ' Hundred Mahashankh ' WHEN 13 THEN ' Thousand Mahashankh '
ELSE '' END
+ @outputString
END
ELSE BEGIN -- break down the ones and the tens separately

SET @outputString = ' '
+ (SELECT word
FROM @NumbersTable
WHERE @tens + '0' = number)
+ '-'
+ (SELECT word
FROM @NumbersTable
WHERE '0'+ @ones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' lakh '
WHEN 4 THEN ' Crore ' WHEN 5 THEN ' Arawb '
WHEN 6 THEN ' Kharawb ' WHEN 7 THEN ' Neel '
WHEN 8 THEN ' Padma ' WHEN 9 THEN ' Shankh '
WHEN 10 THEN ' Mahashankh ' WHEN 11 THEN ' Ten Mahashankh '
WHEN 12 THEN ' Hundred Mahashankh ' WHEN 13 THEN ' Thousand Mahashankh '
ELSE '' END
+ @outputString
END

-- now get the hundreds
IF @hundreds <> '0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE '0' + @hundreds = number)
+ ' hundred '
+ @outputString
END
END

SELECT @counter = @counter + 1
, @position = @position - 2

SET @chunk = RIGHT('00' + SUBSTRING(@inputNumber, @position, 2), 2)
END

-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)


RETURN @outputString -- return the result
END

-- Enjoy!

Here I am... the wind...

Just like the wind,
I've always been
Drifting high up in the sky that never ends
Through thick and thin,
I always win
'Cause I would fight both life and death to save a friend

I face my destiny every day I live
And the best in me is all I have to give

Just like the sun (Just like the sun)
When my day's done
Sometimes I don't like the person I've become
Is the enemy within a thousand men?
Should I walk the path if my world's so dead ahead?

Is someone testing me every day I live?
Well, the best in me is all I have to give

I can pretend (I can pretend)
I am the wind (I am the wind)
And I don't know if I will pass this way again
All things must end
Goodbye, my friend
Think of me when you see the sun or feel the wind

I am the wind,
I am the sun
And one day we'll all be one

I am the wind,
I am the sun
And one day we'll all be one

I am the wind,
I am the sun
And one day we'll all be one

I am the wind,
I am the sun
One day we'll all be one

Lyrics/Composition: A BONE Performed by A BONE feat.Y