· 10 years ago · Aug 30, 2016, 07:20 AM
1if object_id(N'HumanizedStrings','U') is not null
2drop table HumanizedStrings
3go
4
5create table HumanizedStrings
6(
7Number smallint,
8Gender nvarchar(1),
9StringRepresentation nvarchar(15)
10)
11go
12
13create unique index IDX_UNQ_Humanized_NumberGender on HumanizedStrings(Number,Gender)
14go
15
16insert into HumanizedStrings
17select 0,'M','zero' union all
18select 1,'M','unu' union all
19select 1,'F','o' union all
20select 2,'M','doi' union all
21select 2,'F','doua' union all
22select 3,'M','trei' union all
23select 4,'M','patru' union all
24select 5,'M','cinci' union all
25select 6,'M','sase' union all
26select 6,'F','sai' union all
27select 7,'M','sapte' union all
28select 8,'M','opt' union all
29select 9,'M','noua' union all
30select 10,'M','zece' union all
31select 11,'M','unsprezece' union all
32select 12,'M','doisprezece' union all
33select 13,'M','treisprezece' union all
34select 14,'M','paisprezece' union all
35select 15,'M','cincisprezece' union all
36select 16,'M','saiseprezece' union all
37select 17,'M','saptesprezece' union all
38select 18,'M','optsprezece' union all
39select 19,'M','nouasprezece'
40go
41
42if object_id(N'UDF_Humanize_TenthsOnly','FN') is not null
43drop function UDF_Humanize_TenthsOnly
44go
45
46create function dbo.UDF_Humanize_TenthsOnly(@Tenths numeric(2,0),@IsDecimal bit)
47returns nvarchar(100)
48as
49begin
50
51 declare @TenthsInt smallint, @TenthsText nvarchar(100) = ''
52 select @TenthsInt = convert(smallint,@Tenths)
53
54
55
56 declare @1stDigit smallint, @2ndDigit smallint
57
58 select @1stDigit = @TenthsInt % 10
59 select @2ndDigit = convert(int,round(@TenthsInt / 10,0)) % 10
60
61 if(@IsDecimal = 1 and @2ndDigit = 0)
62 begin
63 select @TenthsText = 'zero ' + StringRepresentation from HumanizedStrings where Number = @TenthsInt and Gender = 'M'
64 end
65 else
66 if exists (select 1 from HumanizedStrings where Number = @TenthsInt and Gender = 'M')
67 begin
68 select @TenthsText = StringRepresentation from HumanizedStrings where Number = @TenthsInt and Gender = 'M'
69 end
70 else
71 begin
72
73 if exists (select 1 from HumanizedStrings where Number = @2ndDigit and Gender = 'F')
74 select @TenthsText += (select StringRepresentation from HumanizedStrings where Number = @2ndDigit and Gender = 'F')
75 + 'zeci ' + isnull((select 'si ' + StringRepresentation from HumanizedStrings where Number = @1stDigit and Gender = 'M' and @1stDigit != 0),'')
76 else
77 select @TenthsText += (select StringRepresentation from HumanizedStrings where Number = @2ndDigit and Gender = 'M')
78 + 'zeci ' + isnull((select 'si ' + StringRepresentation from HumanizedStrings where Number = @1stDigit and Gender = 'M' and @1stDigit != 0),'')
79 end
80
81 return @TenthsText
82
83
84end
85go
86
87if object_id(N'UDF_Humanize_Number','FN') is not null
88drop function UDF_Humanize_Number
89go
90
91create function dbo.UDF_Humanize_Number(@Number numeric(6,2),@CCY nvarchar(3))
92returns nvarchar(500)
93as
94begin
95
96 declare @HumanizedString nvarchar(500) = '', @IntegerPart int, @DecimalPart int
97
98 select @IntegerPart = convert(int,substring(convert(nvarchar(7),@Number),1,charindex('.',convert(nvarchar(7),@Number))-1))
99 select @DecimalPart = convert(int,substring(convert(nvarchar(7),@Number),charindex('.',convert(nvarchar(7),@Number))+1,len(convert(nvarchar(7),@Number))))
100
101 declare @1stDigit smallint, @2ndDigit smallint, @3rdDigit smallint, @4thDigit smallint
102
103 select @1stDigit = @IntegerPart % 10
104 select @2ndDigit = convert(int,round(@IntegerPart / 10,0)) % 10
105 select @3rdDigit = convert(int,round(@IntegerPart / 100,0)) % 10
106 select @4thDigit = convert(int,round(@IntegerPart / 1000,0)) % 10
107
108 if(@Number = 0)
109 begin
110
111 select @HumanizedString = 'zero'
112
113 end
114 else
115 begin
116
117 if (@4thDigit != 0)
118 begin
119 if (@4thDigit = 1)
120 select @HumanizedString += 'o mie '
121 else
122 if exists (select 1 from HumanizedStrings where Number = @4thDigit and Gender = 'F' and @4thDigit != 6)
123 select @HumanizedString += (select StringRepresentation from HumanizedStrings where Number = @4thDigit and Gender = 'F') + ' mii '
124 else
125 select @HumanizedString += (select StringRepresentation from HumanizedStrings where Number = @4thDigit and Gender = 'M') + ' mii '
126 end
127
128 if (@3rdDigit != 0)
129 begin
130 if (@3rdDigit = 1)
131 select @HumanizedString += 'o suta '
132 else
133 if exists (select 1 from HumanizedStrings where Number = @3rdDigit and Gender = 'F' and @3rdDigit != 6)
134 select @HumanizedString += (select StringRepresentation from HumanizedStrings where Number = @3rdDigit and Gender = 'F') + ' sute '
135 else
136 select @HumanizedString += (select StringRepresentation from HumanizedStrings where Number = @3rdDigit and Gender = 'M') + ' sute '
137 end
138
139 if @2ndDigit != 0 or @1stDigit != 0
140 select @HumanizedString += dbo.UDF_Humanize_TenthsOnly(convert(int,@2ndDigit * 10 + @1stDigit),0)
141
142 if (@DecimalPart != 0)
143 select @HumanizedString += ' virgula ' + dbo.UDF_Humanize_TenthsOnly(@DecimalPart,1)
144
145 end
146
147 if (upper(@CCY) = 'RON')
148 SELECT @HumanizedString += ' lei'
149 else
150 if (upper(@CCY) = 'EUR')
151 SELECT @HumanizedString += ' euro'
152 select @HumanizedString = replace(@HumanizedString,' ',' ')
153 return @HumanizedString
154
155end
156go
157
158
159if object_id(N'tempdb..#TempTrySQLROHumanizer') is not null
160drop table #TempTrySQLROHumanizer
161
162
163declare @i int = 0, @RandomNumber numeric(6,2), @CCY nvarchar(3)
164
165create table #TempTrySQLROHumanizer
166(
167Number numeric(6,2) not null,
168Currency nvarchar(3) not null,
169ROHumanizedNumber nvarchar(500) not null
170)
171
172
173while @i < 1000
174begin
175
176 select @RandomNumber = convert(numeric(6,2),round(rand() * power(10,4),2))
177 select @CCY = iif(round(@RandomNumber,0) % 2 = 0, 'RON','EUR')
178
179 insert into #TempTrySQLROHumanizer
180 (Number,Currency,ROHumanizedNumber)
181 select @RandomNumber, @CCY, dbo.UDF_Humanize_Number(@RandomNumber,@CCY)
182
183 set @i = @i + 1
184
185end
186
187select *
188from #TempTrySQLROHumanizer