SSRS amount convert in word INR

Question

i want amount 12345678.99 converted in INR Value like below mention

*in word : one crore twenty three lakh forty five thousand seventy eight and ninety nine paisa. *

please provide code function also another ways if have

Answer 1

Here is a piece of code that does something similar for Dollar, Punds and Euro. You have to tweek it to suit your needs for INR!

You can call the function like so :

declare @currency decimal(13,2) = 1000345.20

select dbo.f_CurrencyToWords(@currency,3) words

The functions used above :

CREATE FUNCTION [dbo].[f_CurrencyToWordsSub](@Input varchar(3),@Group tinyint,@Currency tinyint)

RETURNS varchar(8000)
AS
BEGIN

SET @Input = right('000' + @Input,3)

DECLARE @Groups TABLE
([Group] tinyint
,Currency tinyint
,Word varchar(10))

INSERT INTO @Groups
SELECT 5,NULL,'Billion '
UNION
SELECT 4,NULL,'Million '
UNION
SELECT 3,NULL,'Thousand, '
UNION
SELECT 2,1,'Pound# '
UNION
SELECT 2,2,'Euro# '
UNION
SELECT 2,3,'Dollar# '
UNION
SELECT 1,1,'Pence '
UNION
SELECT 1,2,'Cent~ '
UNION
SELECT 1,3,'Cent~ '

DECLARE @Numbers TABLE
(Number char(2)
,Word varchar(10))

INSERT INTO @Numbers
SELECT '01','One ' UNION SELECT '02','Two ' UNION SELECT '03','Three ' UNION SELECT '04','Four ' UNION SELECT '05','Five ' UNION SELECT '06','Six ' UNION SELECT '07','Seven ' UNION SELECT '08','Eight ' UNION SELECT '09','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','Forty ' UNION SELECT '50','Fifty ' UNION SELECT '60','Sixty ' UNION SELECT '70','Seventy ' UNION SELECT '80','Eighty ' UNION SELECT '90','Ninety '

DECLARE @Output varchar(100)

SET @Output = 
CASE --Hundreds
WHEN left(@Input,1) <> 0 THEN (SELECT Word FROM @Numbers WHERE Number = '0' + left(@Input,1)) + 'Hundred '
ELSE ''
END +
CASE --And
WHEN @Group <> 1 AND right(@Input,2) <> 0 AND @Input > 100 THEN 'And '
ELSE ''
END +
CASE --Units
WHEN @Input = 0 AND @Group = 1 THEN 'Zero '
WHEN right(@Input,2) BETWEEN 1 AND 20 THEN (SELECT Word FROM @Numbers WHERE Number = right(@Input,2))
WHEN right(@Input,2) > 20 THEN (SELECT Word FROM @Numbers WHERE Number = left(right(@Input,2),1) + '0') + ISNULL((SELECT Word FROM @Numbers WHERE Number = '0' + right(@Input,1)),'')
ELSE ''
END +
CASE
WHEN @Group = 2 OR (@Group <> 2 AND @Input <> 0) THEN (SELECT Word FROM @Groups WHERE [Group] = @Group AND ISNULL(Currency,@Currency) = @Currency)
ELSE ''
END

RETURN @Output

END

The above function is called in the following one :

CREATE FUNCTION [dbo].[f_CurrencyToWords] (@Input numeric(15,2),@Currency tinyint) 
RETURNS varchar (8000) 
AS 
BEGIN 

DECLARE @CharInput char(15) 
--Pad the input
SET @CharInput = RIGHT('0000000000000' + convert(varchar(15),@Input),15) 

DECLARE @Counter tinyint
SET @Counter = 1

DECLARE @InputSub varchar(3)
DECLARE @Group tinyint
DECLARE @Output varchar (8000)

--Get words for each group with some logic for concatenation
WHILE @Counter <= len(@CharInput)-2
BEGIN
SET @InputSub = replace(substring(@CharInput,@Counter,3),'.','')
SET @Group = (len(@CharInput)-@Counter+1)/3

SET @Output = 
ISNULL(@Output,'') 
+ CASE 
WHEN (@Group = 1 AND @InputSub <> 0 AND @Input >= 1.00) OR (@Group = 2 AND @InputSub BETWEEN 1 AND 99 AND @Input > 1000) THEN 'And '
ELSE ''
END
+ CASE
WHEN (@Group = 1 AND @InputSub = 0 AND @Input >= 1.00) OR (@Group = 2 AND @InputSub = 0 AND @Input < 1.00) THEN ''
ELSE [dbo].[f_CurrencyToWordsSub](@InputSub,@Group,@Currency)
END

SET @Counter = @Counter + 3
CONTINUE
END

--Fix plurals and return a plain Zero if required
SET @Output = 
CASE 
WHEN right(@CharInput,2) = '01' THEN replace(@Output,'~','')
ELSE replace(@Output,'~','s')
END
SET @Output = 
CASE 
WHEN @Input < 2 THEN replace(@Output,'#','')
ELSE replace(@Output,'#','s')
END

RETURN ltrim(rtrim(@Output))
END