Romanian Number Humanizer in SQL Server
This is for my romanian friends around here who, just like me, may some day run into the situation where they'll have to humanize numbers into romanian words.
This code is for SQL Server and it's more for financial institutions where you have to convert an amount into words (eg: payment orders). It supports conversion of values up to 9999.99 but it can be easily modified to go even further and/or be easily customized for every other language.
The script below includes a test case as well. Copy-paste it and give it a try.
if object_id(N'HumanizedStrings','U') is not null
drop table HumanizedStrings
go
create table HumanizedStrings
(
Number smallint,
Gender nvarchar(1),
StringRepresentation nvarchar(15)
)
go
create unique index IDX_UNQ_Humanized_NumberGender on HumanizedStrings(Number,Gender)
go
insert into HumanizedStrings
select 0,'M','zero' union all
select 1,'M','unu' union all
select 1,'F','o' union all
select 2,'M','doi' union all
select 2,'F','doua' union all
select 3,'M','trei' union all
select 4,'M','patru' union all
select 5,'M','cinci' union all
select 6,'M','sase' union all
select 6,'F','sai' union all
select 7,'M','sapte' union all
select 8,'M','opt' union all
select 9,'M','noua' union all
select 10,'M','zece' union all
select 11,'M','unsprezece' union all
select 12,'M','doisprezece' union all
select 13,'M','treisprezece' union all
select 14,'M','paisprezece' union all
select 15,'M','cincisprezece' union all
select 16,'M','saiseprezece' union all
select 17,'M','saptesprezece' union all
select 18,'M','optsprezece' union all
select 19,'M','nouasprezece'
go
if object_id(N'UDF_Humanize_TenthsOnly','FN') is not null
drop function UDF_Humanize_TenthsOnly
go
create function dbo.UDF_Humanize_TenthsOnly(@Tenths numeric(2,0),@IsDecimal bit)
returns nvarchar(100)
as
begin
declare @TenthsInt smallint, @TenthsText nvarchar(100) = ''
select @TenthsInt = convert(smallint,@Tenths)
declare @1stDigit smallint, @2ndDigit smallint
select @1stDigit = @TenthsInt % 10
select @2ndDigit = convert(int,round(@TenthsInt / 10,0)) % 10
if(@IsDecimal = 1 and @2ndDigit = 0)
begin
select @TenthsText = 'zero ' + StringRepresentation from HumanizedStrings where Number = @TenthsInt and Gender = 'M'
end
else
if exists (select 1 from HumanizedStrings where Number = @TenthsInt and Gender = 'M')
begin
select @TenthsText = StringRepresentation from HumanizedStrings where Number = @TenthsInt and Gender = 'M'
end
else
begin
if exists (select 1 from HumanizedStrings where Number = @2ndDigit and Gender = 'F')
select @TenthsText += (select StringRepresentation from HumanizedStrings where Number = @2ndDigit and Gender = 'F')
+ 'zeci ' + isnull((select 'si ' + StringRepresentation from HumanizedStrings where Number = @1stDigit and Gender = 'M' and @1stDigit != 0),'')
else
select @TenthsText += (select StringRepresentation from HumanizedStrings where Number = @2ndDigit and Gender = 'M')
+ 'zeci ' + isnull((select 'si ' + StringRepresentation from HumanizedStrings where Number = @1stDigit and Gender = 'M' and @1stDigit != 0),'')
end
return @TenthsText
end
go
if object_id(N'UDF_Humanize_Number','FN') is not null
drop function UDF_Humanize_Number
go
create function dbo.UDF_Humanize_Number(@Number numeric(6,2),@CCY nvarchar(3))
returns nvarchar(500)
as
begin
declare @HumanizedString nvarchar(500) = '', @IntegerPart int, @DecimalPart int
select @IntegerPart = convert(int,substring(convert(nvarchar(7),@Number),1,charindex('.',convert(nvarchar(7),@Number))-1))
select @DecimalPart = convert(int,substring(convert(nvarchar(7),@Number),charindex('.',convert(nvarchar(7),@Number))+1,len(convert(nvarchar(7),@Number))))
declare @1stDigit smallint, @2ndDigit smallint, @3rdDigit smallint, @4thDigit smallint
select @1stDigit = @IntegerPart % 10
select @2ndDigit = convert(int,round(@IntegerPart / 10,0)) % 10
select @3rdDigit = convert(int,round(@IntegerPart / 100,0)) % 10
select @4thDigit = convert(int,round(@IntegerPart / 1000,0)) % 10
if(@Number = 0)
begin
select @HumanizedString = 'zero'
end
else
begin
if (@4thDigit != 0)
begin
if (@4thDigit = 1)
select @HumanizedString += 'o mie '
else
if exists (select 1 from HumanizedStrings where Number = @4thDigit and Gender = 'F' and @4thDigit != 6)
select @HumanizedString += (select StringRepresentation from HumanizedStrings where Number = @4thDigit and Gender = 'F') + ' mii '
else
select @HumanizedString += (select StringRepresentation from HumanizedStrings where Number = @4thDigit and Gender = 'M') + ' mii '
end
if (@3rdDigit != 0)
begin
if (@3rdDigit = 1)
select @HumanizedString += 'o suta '
else
if exists (select 1 from HumanizedStrings where Number = @3rdDigit and Gender = 'F' and @3rdDigit != 6)
select @HumanizedString += (select StringRepresentation from HumanizedStrings where Number = @3rdDigit and Gender = 'F') + ' sute '
else
select @HumanizedString += (select StringRepresentation from HumanizedStrings where Number = @3rdDigit and Gender = 'M') + ' sute '
end
if @2ndDigit != 0 or @1stDigit != 0
select @HumanizedString += dbo.UDF_Humanize_TenthsOnly(convert(int,@2ndDigit * 10 + @1stDigit),0)
if (@DecimalPart != 0)
select @HumanizedString += ' virgula ' + dbo.UDF_Humanize_TenthsOnly(@DecimalPart,1)
end
if (upper(@CCY) = 'RON')
SELECT @HumanizedString += ' lei'
else
if (upper(@CCY) = 'EUR')
SELECT @HumanizedString += ' euro'
select @HumanizedString = replace(@HumanizedString,' ',' ')
return @HumanizedString
end
go
if object_id(N'tempdb..#TempTrySQLROHumanizer') is not null
drop table #TempTrySQLROHumanizer
declare @i int = 0, @RandomNumber numeric(6,2), @CCY nvarchar(3)
create table #TempTrySQLROHumanizer
(
Number numeric(6,2) not null,
Currency nvarchar(3) not null,
ROHumanizedNumber nvarchar(500) not null
)
while @i < 1000
begin
select @RandomNumber = convert(numeric(6,2),round(rand() * power(10,4),2))
select @CCY = iif(round(@RandomNumber,0) % 2 = 0, 'RON','EUR')
insert into #TempTrySQLROHumanizer
(Number,Currency,ROHumanizedNumber)
select @RandomNumber, @CCY, dbo.UDF_Humanize_Number(@RandomNumber,@CCY)
set @i = @i + 1
end
select *
from #TempTrySQLROHumanizer