Convert numeric value to Indian currency format in word.
I know it is not that much crucial but neither easy to get
rid of financial application’s requirements as clients demand more and more
facilities even they are small in size.
Here is one that SQL Script that every finance domain expert
looks for. That is the currency writer in WORDS instead of digits.
Its return word in Indian currency format
Example
select dbo.[NumToWords](1110011011)
o/p ONE
ARAB ELEVEN CRORE ELEVEN THOUSAND ELEVEN
RS
CODE
/******
Object: UserDefinedFunction [dbo].[NumToWords]
Script Date: 12/13/2012 07:04:34
Created by :- Nilesh Makavana
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[NumToWords]
(
@InNumericValue NUMERIC(38,2)
)
RETURNS VARCHAR(1000)
AS
BEGIN
--declare
@InNumericValue NUMERIC(38,2) = 109
DECLARE @RetVal VARCHAR(60),@StrRight VARCHAR(5),@StrFinal VARCHAR(60),@StrLength INT
SET @RetVal = ''
SET @RetVal= @InNumericValue
SET @StrLength
= LEN(@RetVal)
--create a
virtual table
declare @sequence as table (seq INTEGER NOT NULL UNIQUE, word
[varchar](25) NOT NULL)
INSERT INTO @sequence SELECT
0, '' INSERT INTO @sequence
SELECT 1, 'One ' INSERT INTO @sequence SELECT
2, 'Two '
INSERT INTO @sequence SELECT
3, 'Three ' INSERT INTO @sequence
SELECT 4, 'Four ' INSERT INTO @sequence SELECT
5, 'Five '
INSERT INTO @sequence SELECT
6, 'Six ' INSERT INTO @sequence
SELECT 7, 'Seven ' INSERT INTO @sequence SELECT
8, 'Eight '
INSERT INTO @sequence SELECT
9, 'Nine ' INSERT INTO @sequence
SELECT 10, 'Ten ' INSERT INTO @sequence SELECT
11, 'Eleven '
INSERT INTO @sequence SELECT
12, 'Twelve ' INSERT INTO @sequence
SELECT 13, 'Thirteen ' INSERT INTO @sequence SELECT
14, 'Fourteen '
INSERT INTO @sequence SELECT
15, 'Fifteen ' INSERT INTO @sequence
SELECT 16, 'Sixteen ' INSERT INTO @sequence SELECT
17, 'Seventeen '
INSERT INTO @sequence SELECT
18, 'Eighteen ' INSERT INTO @sequence
SELECT 19, 'Nineteen ' INSERT INTO @sequence SELECT
20, 'Twenty '
INSERT INTO @sequence SELECT
30, 'Thirty ' INSERT INTO @sequence
SELECT 40, 'Forty ' INSERT INTO @sequence SELECT
50, 'Fifty '
INSERT INTO @sequence SELECT
60, 'Sixty ' INSERT INTO @sequence
SELECT 70, 'Seventy ' INSERT INTO @sequence SELECT
80, 'Eighty '
INSERT INTO @sequence SELECT
90, 'Ninty '
IF(@StrLength > 6)
BEGIN
SET @StrFinal = RIGHT(@RetVal,6)
SET @RetVal = SUBSTRING(@RetVal,-5,@StrLength)
SET @StrLength = LEN(@RetVal)
IF (LEN(@RetVal) > 0 AND LEN(@RetVal) < 3)
BEGIN
SET @StrFinal = @RetVal + ',' + @StrFinal
END
WHILE LEN(@RetVal) > 2
BEGIN
SET @StrRight = RIGHT(@RetVal,2)
SET @StrFinal = @StrRight + ',' + @StrFinal
SET @RetVal = SUBSTRING(@RetVal,-1,@StrLength)
SET @StrLength = LEN(@RetVal)
IF(LEN(@RetVal) > 2)
CONTINUE
ELSE
SET @StrFinal = @RetVal + ',' + @StrFinal
BREAK
END
END
ELSE
BEGIN
SET @StrFinal = @RetVal
END
SELECT @StrFinal = ISNULL(@StrFinal,00)
declare
@TbStrFinal as table(id int,value varchar(6))
insert into @TbStrFinal
select * FROM dbo.Split(@StrFinal,',')
--select * from
##StrFinal order by 1 desc
declare @totRow int
declare @counter int = 1
declare @finalWord
varchar(1000) = ''
declare @num numeric(10,2)
select @totRow = count(*) from @TbStrFinal
while @totrow > 0
begin
select @num = value from
@TbStrFinal where id =
@totrow
if @counter = 1
BEGIN
if convert(int,@num) >= 100
begin
SELECT @finalWord = case when len(THundreds.word) > 0 then THundreds.word + 'Hundred ' else '' end
+ case when (select len(word) from @sequence where seq = ((convert(int,@num) % 100))) > 0 then
'and ' else '' end
+ case when TTens.seq > 19 then + '' + TTens.word + TUnits.word else
(select + '' + word from @sequence
where seq = ((convert(int,@num) % 100))) end
FROM @sequence AS TUnits
CROSS JOIN @sequence AS
TTens
CROSS JOIN @sequence AS
THundreds
WHERE TUnits.seq = (convert(int,@num) % 100) % 10
AND TTens.seq = (convert(int,@num) % 100) - (convert(int,@num) % 100) % 10
AND THundreds.seq = (convert(int,@num) / 100)
end
else
begin
SELECT @finalWord = case when TTens.seq > 19 then + '' + TTens.word + TUnits.word else
(select + '' + word from @sequence
where seq = ((convert(int,@num) % 100))) end
FROM @sequence AS TUnits
CROSS JOIN @sequence AS
TTens
WHERE TUnits.seq = (convert(int,@num) % 100) % 10
AND TTens.seq = (convert(int,@num) % 100) - (convert(int,@num) % 100) % 10
end
END
if @counter = 2
BEGIN
set @finalWord = (select case
when @num = 0 then ''
when @num < 19 and @num <> 0 then (select word + 'Thousand ' from @sequence where
seq = @num)
else
(select TTens.word + TUnits.word + 'Thousand ' FROM
@sequence AS TUnits CROSS
JOIN @sequence AS
TTens
where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) + @finalWord
END
if @counter = 3
BEGIN
set @finalWord = (select case
when @num = 0 then ''
when @num < 19 and @num <> 0 then (select word + 'lac ' from @sequence where
seq = @num)
else
(select TTens.word + TUnits.word + 'lac ' FROM @sequence AS TUnits CROSS JOIN @sequence AS
TTens
where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) + @finalWord
END
if @counter = 4
BEGIN
set @finalWord = (select case
when @num = 0 then ''
when @num < 19 and @num <> 0 then (select word + 'crore ' from @sequence where
seq = @num)
else
(select TTens.word + TUnits.word + 'crore ' FROM
@sequence AS TUnits CROSS
JOIN @sequence AS
TTens
where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) + @finalWord
END
if @counter = 5
BEGIN
set @finalWord = (select case
when @num = 0 then ''
when @num < 19 and @num <> 0 then (select word + 'arab ' from
@sequence where seq =
@num)
else
(select TTens.word + TUnits.word + 'arab ' FROM @sequence
AS TUnits CROSS
JOIN @sequence AS
TTens
where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) + @finalWord
END
if @counter = 6
BEGIN
set @finalWord = (select case
when @num = 0 then ''
when @num < 19 and @num <> 0 then (select word + 'kharab ' from
@sequence where seq =
@num)
else
(select TTens.word + TUnits.word + 'kharab ' FROM
@sequence AS TUnits CROSS
JOIN @sequence AS
TTens
where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) + @finalWord
END
if @counter = 7
BEGIN
set @finalWord = (select case
when @num = 0 then ''
when @num < 19 and @num <> 0 then (select word + 'marab ' from
@sequence where seq =
@num)
else
(select TTens.word + TUnits.word + 'marab ' FROM
@sequence AS TUnits CROSS
JOIN @sequence AS
TTens
where TUnits.seq = (@num % 10) AND TTens.seq = ((@num % 100)-(@num % 10))) end ) + @finalWord
END
set @totrow = @totrow - 1
set @counter = @counter + 1
end
RETURN UPPER(@finalWord + 'rs')
END
Here split function also used. for view that code go to this post.
