Code:
CREATE FUNCTION [dbo].[curto_word](@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
CREATE FUNCTION [dbo].[udf_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].[curto_word](@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
Bookmarks