CREATE FUNCTION [dbo].[spell_number]
( @Input int )
RETURNS varchar(8000) AS
BEGIN
Declare @Number Numeric(38,0)
set @Number = @Input
Declare @Cents as int
set @Cents = 100*Convert(money,(@Input - convert(Numeric(38,3),@Number)))
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)
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)/3
-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1
-- insert data for the numbers and words
INSERT INTO @NumbersTable SELECT '00', ''
UNION ALL SELECT '1' , 'First' UNION ALL SELECT '2', 'Second'
UNION ALL SELECT '3' , 'Third' UNION ALL SELECT '4', 'Fourth'
UNION ALL SELECT '01', 'One' UNION ALL SELECT '02', 'Two'
UNION ALL SELECT '03', 'Third' 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'
WHILE @counter <= @loops
BEGIN
-- get chunks of 3 numbers at a time, padded with leading zeros
SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)
IF @chunk <> '000'
BEGIN
SELECT @tensones = SUBSTRING(@chunk, 2, 2)
, @hundreds = SUBSTRING(@chunk, 1, 1)
, @tens = SUBSTRING(@chunk, 2, 1)
, @ones = SUBSTRING(@chunk, 3, 1)
-- If twenty or less, use the word directly from @NumbersTable
IF CONVERT(INT, @tensones) <= 20 OR @Ones='0'
BEGIN
IF @length = 1 AND (@tensones = 01 OR @tensones = 02 OR @tensones = 03 OR @tensones = 04 )
BEGIN
SET @outputString = (SELECT word FROM @NumbersTable WHERE number = CONVERT (VARCHAR , CONVERT ( INT , @tensones ), 103) )
END
ELSE
BEGIN
SET @outputString = (SELECT word FROM @NumbersTable WHERE @tensones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
+ @outputString
END
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 ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
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 - 3
END
-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)
RETURN UPPER(@outputString)
END
Answer:
select [dbo].[spell_number] (10100)
O/P : TEN THOUSAND ONE HUNDRED
select [dbo].[spell_number] (10101010)
O/P : TEN MILLION ONE HUNDRED ONE THOUSAND TEN
No comments:
Post a Comment