T-SQL汉字转全拼

发布于 作者 量尺寸留下评论

创建一个T-SQL函数即可实现将汉字转为全拼。,这在使用姓名、地名进行模糊查询时非常有用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
CREATE FUNCTION [dbo].procGetPinYin(@str VARCHAR(100))
RETURNS VARCHAR(8000)
AS
BEGIN
 DECLARE @re VARCHAR(8000),@crs VARCHAR(10)
 DECLARE @strlen INT 
 SELECT @strlen=len(@str),@re=''
 while @strlen>0
 BEGIN  
  SET @crs= SUBSTRING(@str,@strlen,1)
      SELECT @re=
        CASE
        WHEN @crs<'吖' THEN @crs
        WHEN @crs<='厑' THEN 'a'
        WHEN @crs<='靉' THEN 'ai'
        WHEN @crs<='黯' THEN 'an'
        WHEN @crs<='醠' THEN 'ang'
        WHEN @crs<='驁' THEN 'ao'
        WHEN @crs<='欛' THEN 'ba'
        WHEN @crs<='瓸' THEN 'bai'
        WHEN @crs<='瓣' THEN 'ban'
        WHEN @crs<='鎊' THEN 'bang'
        WHEN @crs<='鑤' THEN 'bao'
        WHEN @crs<='鐾' THEN 'bei'
        WHEN @crs<='輽' THEN 'ben'
        WHEN @crs<='鏰' THEN 'beng'
        WHEN @crs<='鼊' THEN 'bi'
        WHEN @crs<='變' THEN 'bian'
        WHEN @crs<='鰾' THEN 'biao'
        WHEN @crs<='彆' THEN 'bie'
        WHEN @crs<='鬢' THEN 'bin'
        WHEN @crs<='靐' THEN 'bing'
        WHEN @crs<='蔔' THEN 'bo'
        WHEN @crs<='簿' THEN 'bu'
        WHEN @crs<='囃' THEN 'ca'
        WHEN @crs<='乲' THEN 'cai'
        WHEN @crs<='爘' THEN 'can'
        WHEN @crs<='賶' THEN 'cang'
        WHEN @crs<='鼜' THEN 'cao'
        WHEN @crs<='簎' THEN 'ce'
        WHEN @crs<='笒' THEN 'cen'
        WHEN @crs<='乽' THEN 'ceng'
        WHEN @crs<='詫' THEN 'cha'
        WHEN @crs<='囆' THEN 'chai'
        WHEN @crs<='顫' THEN 'chan'
        WHEN @crs<='韔' THEN 'chang'
        WHEN @crs<='觘' THEN 'chao'
        WHEN @crs<='爡' THEN 'che'
        WHEN @crs<='讖' THEN 'chen'
        WHEN @crs<='秤' THEN 'cheng'
        WHEN @crs<='鷘' THEN 'chi'
        WHEN @crs<='銃' THEN 'chong'
        WHEN @crs<='殠' THEN 'chou'
        WHEN @crs<='矗' THEN 'chu'
        WHEN @crs<='踹' THEN 'chuai'
        WHEN @crs<='鶨' THEN 'chuan'
        WHEN @crs<='愴' THEN 'chuang'
        WHEN @crs<='顀' THEN 'chui'
        WHEN @crs<='蠢' THEN 'chun'
        WHEN @crs<='縒' THEN 'chuo'
        WHEN @crs<='嗭' THEN 'ci'
        WHEN @crs<='謥' THEN 'cong'
        WHEN @crs<='輳' THEN 'cou'
        WHEN @crs<='顣' THEN 'cu'
        WHEN @crs<='爨' THEN 'cuan'
        WHEN @crs<='臎' THEN 'cui'
        WHEN @crs<='籿' THEN 'cun'
        WHEN @crs<='錯' THEN 'cuo'
        WHEN @crs<='橽' THEN 'da'
        WHEN @crs<='靆' THEN 'dai'
        WHEN @crs<='饏' THEN 'dan'
        WHEN @crs<='闣' THEN 'dang'
        WHEN @crs<='纛' THEN 'dao'
        WHEN @crs<='的' THEN 'de'
        WHEN @crs<='扽' THEN 'den'
        WHEN @crs<='鐙' THEN 'deng'
        WHEN @crs<='螮' THEN 'di'
        WHEN @crs<='嗲' THEN 'dia'
        WHEN @crs<='驔' THEN 'dian'
        WHEN @crs<='鑃' THEN 'diao'
        WHEN @crs<='嚸' THEN 'die'
        WHEN @crs<='顁' THEN 'ding'
        WHEN @crs<='銩' THEN 'diu'
        WHEN @crs<='霘' THEN 'dong'
        WHEN @crs<='鬭' THEN 'dou'
        WHEN @crs<='蠹' THEN 'du'
        WHEN @crs<='叾' THEN 'duan'
        WHEN @crs<='譵' THEN 'dui'
        WHEN @crs<='踲' THEN 'dun'
        WHEN @crs<='鵽' THEN 'duo'
        WHEN @crs<='鱷' THEN 'e'
        WHEN @crs<='摁' THEN 'en'
        WHEN @crs<='鞥' THEN 'eng'
        WHEN @crs<='樲' THEN 'er'
        WHEN @crs<='髮' THEN 'fa'
        WHEN @crs<='瀪' THEN 'fan'
        WHEN @crs<='放' THEN 'fang'
        WHEN @crs<='靅' THEN 'fei'
        WHEN @crs<='鱝' THEN 'fen'
        WHEN @crs<='覅' THEN 'feng'
        WHEN @crs<='梻' THEN 'fo'
        WHEN @crs<='鴀' THEN 'fou'
        WHEN @crs<='猤' THEN 'fu'
        WHEN @crs<='魀' THEN 'ga'
        WHEN @crs<='瓂' THEN 'gai'
        WHEN @crs<='灨' THEN 'gan'
        WHEN @crs<='戇' THEN 'gang'
        WHEN @crs<='鋯' THEN 'gao'
        WHEN @crs<='獦' THEN 'ge'
        WHEN @crs<='給' THEN 'gei'
        WHEN @crs<='搄' THEN 'gen'
        WHEN @crs<='堩' THEN 'geng'
        WHEN @crs<='兣' THEN 'gong'
        WHEN @crs<='購' THEN 'gou'
        WHEN @crs<='顧' THEN 'gu'
        WHEN @crs<='詿' THEN 'gua'
        WHEN @crs<='恠' THEN 'guai'
        WHEN @crs<='鱹' THEN 'guan'
        WHEN @crs<='撗' THEN 'guang'
        WHEN @crs<='鱥' THEN 'gui'
        WHEN @crs<='謴' THEN 'gun'
        WHEN @crs<='腂' THEN 'guo'
        WHEN @crs<='哈' THEN 'ha'
        WHEN @crs<='饚' THEN 'hai'
        WHEN @crs<='鶾' THEN 'han'
        WHEN @crs<='沆' THEN 'hang'
        WHEN @crs<='兞' THEN 'hao'
        WHEN @crs<='靏' THEN 'he'
        WHEN @crs<='嬒' THEN 'hei'
        WHEN @crs<='恨' THEN 'hen'
        WHEN @crs<='堼' THEN 'heng'
        WHEN @crs<='鬨' THEN 'hong'
        WHEN @crs<='鱟' THEN 'hou'
        WHEN @crs<='鸌' THEN 'hu'
        WHEN @crs<='蘳' THEN 'hua'
        WHEN @crs<='蘾' THEN 'huai'
        WHEN @crs<='鰀' THEN 'huan'
        WHEN @crs<='鎤' THEN 'huang'
        WHEN @crs<='顪' THEN 'hui'
        WHEN @crs<='諢' THEN 'hun'
        WHEN @crs<='夻' THEN 'huo'
        WHEN @crs<='驥' THEN 'ji'
        WHEN @crs<='嗧' THEN 'jia'
        WHEN @crs<='鑳' THEN 'jian'
        WHEN @crs<='謽' THEN 'jiang'
        WHEN @crs<='釂' THEN 'jiao'
        WHEN @crs<='繲' THEN 'jie'
        WHEN @crs<='齽' THEN 'jin'
        WHEN @crs<='竸' THEN 'jing'
        WHEN @crs<='蘔' THEN 'jiong'
        WHEN @crs<='欍' THEN 'jiu'
        WHEN @crs<='爠' THEN 'ju'
        WHEN @crs<='羂' THEN 'juan'
        WHEN @crs<='钁' THEN 'jue'
        WHEN @crs<='攈' THEN 'jun'
        WHEN @crs<='鉲' THEN 'ka'
        WHEN @crs<='乫' THEN 'kai'
        WHEN @crs<='矙' THEN 'kan'
        WHEN @crs<='閌' THEN 'kang'
        WHEN @crs<='鯌' THEN 'kao'
        WHEN @crs<='騍' THEN 'ke'
        WHEN @crs<='褃' THEN 'ken'
        WHEN @crs<='鏗' THEN 'keng'
        WHEN @crs<='廤' THEN 'kong'
        WHEN @crs<='鷇' THEN 'kou'
        WHEN @crs<='嚳' THEN 'ku'
        WHEN @crs<='骻' THEN 'kua'
        WHEN @crs<='鱠' THEN 'kuai'
        WHEN @crs<='窾' THEN 'kuan'
        WHEN @crs<='鑛' THEN 'kuang'
        WHEN @crs<='鑎' THEN 'kui'
        WHEN @crs<='睏' THEN 'kun'
        WHEN @crs<='穒' THEN 'kuo'
        WHEN @crs<='鞡' THEN 'la'
        WHEN @crs<='籟' THEN 'lai'
        WHEN @crs<='糷' THEN 'lan'
        WHEN @crs<='唥' THEN 'lang'
        WHEN @crs<='軂' THEN 'lao'
        WHEN @crs<='餎' THEN 'le'
        WHEN @crs<='脷' THEN 'lei'
        WHEN @crs<='睖' THEN 'leng'
        WHEN @crs<='瓈' THEN 'li'
        WHEN @crs<='倆' THEN 'lia'
        WHEN @crs<='纞' THEN 'lian'
        WHEN @crs<='鍄' THEN 'liang'
        WHEN @crs<='瞭' THEN 'liao'
        WHEN @crs<='鱲' THEN 'lie'
        WHEN @crs<='轥' THEN 'lin'
        WHEN @crs<='炩' THEN 'ling'
        WHEN @crs<='咯' THEN 'liu'
        WHEN @crs<='贚' THEN 'long'
        WHEN @crs<='鏤' THEN 'lou'
        WHEN @crs<='氇' THEN 'lu'
        WHEN @crs<='鑢' THEN 'lv'
        WHEN @crs<='亂' THEN 'luan'
        WHEN @crs<='擽' THEN 'lue'
        WHEN @crs<='論' THEN 'lun'
        WHEN @crs<='鱳' THEN 'luo'
        WHEN @crs<='嘛' THEN 'ma'
        WHEN @crs<='霢' THEN 'mai'
        WHEN @crs<='蘰' THEN 'man'
        WHEN @crs<='蠎' THEN 'mang'
        WHEN @crs<='唜' THEN 'mao'
        WHEN @crs<='癦' THEN 'me'
        WHEN @crs<='嚜' THEN 'mei'
        WHEN @crs<='們' THEN 'men'
        WHEN @crs<='霥' THEN 'meng'
        WHEN @crs<='羃' THEN 'mi'
        WHEN @crs<='麵' THEN 'mian'
        WHEN @crs<='廟' THEN 'miao'
        WHEN @crs<='鱴' THEN 'mie'
        WHEN @crs<='鰵' THEN 'min'
        WHEN @crs<='詺' THEN 'ming'
        WHEN @crs<='謬' THEN 'miu'
        WHEN @crs<='耱' THEN 'mo'
        WHEN @crs<='麰' THEN 'mou'
        WHEN @crs<='旀' THEN 'mu'
        WHEN @crs<='魶' THEN 'na'
        WHEN @crs<='錼' THEN 'nai'
        WHEN @crs<='婻' THEN 'nan'
        WHEN @crs<='齉' THEN 'nang'
        WHEN @crs<='臑' THEN 'nao'
        WHEN @crs<='呢' THEN 'ne'
        WHEN @crs<='焾' THEN 'nei'
        WHEN @crs<='嫩' THEN 'nen'
        WHEN @crs<='能' THEN 'neng'
        WHEN @crs<='嬺' THEN 'ni'
        WHEN @crs<='艌' THEN 'nian'
        WHEN @crs<='釀' THEN 'niang'
        WHEN @crs<='脲' THEN 'niao'
        WHEN @crs<='钀' THEN 'nie'
        WHEN @crs<='拰' THEN 'nin'
        WHEN @crs<='濘' THEN 'ning'
        WHEN @crs<='靵' THEN 'niu'
        WHEN @crs<='齈' THEN 'nong'
        WHEN @crs<='譳' THEN 'nou'
        WHEN @crs<='搙' THEN 'nu'
        WHEN @crs<='衄' THEN 'nv'
        WHEN @crs<='瘧' THEN 'nue'
        WHEN @crs<='燶' THEN 'nuan'
        WHEN @crs<='桛' THEN 'nuo'
        WHEN @crs<='鞰' THEN 'o'
        WHEN @crs<='漚' THEN 'ou'
        WHEN @crs<='袙' THEN 'pa'
        WHEN @crs<='磗' THEN 'pai'
        WHEN @crs<='鑻' THEN 'pan'
        WHEN @crs<='胖' THEN 'pang'
        WHEN @crs<='礮' THEN 'pao'
        WHEN @crs<='轡' THEN 'pei'
        WHEN @crs<='喯' THEN 'pen'
        WHEN @crs<='喸' THEN 'peng'
        WHEN @crs<='鸊' THEN 'pi'
        WHEN @crs<='騙' THEN 'pian'
        WHEN @crs<='慓' THEN 'piao'
        WHEN @crs<='嫳' THEN 'pie'
        WHEN @crs<='聘' THEN 'pin'
        WHEN @crs<='蘋' THEN 'ping'
        WHEN @crs<='魄' THEN 'po'
        WHEN @crs<='哛' THEN 'pou'
        WHEN @crs<='曝' THEN 'pu'
        WHEN @crs<='蟿' THEN 'qi'
        WHEN @crs<='髂' THEN 'qia'
        WHEN @crs<='縴' THEN 'qian'
        WHEN @crs<='瓩' THEN 'qiang'
        WHEN @crs<='躈' THEN 'qiao'
        WHEN @crs<='籡' THEN 'qie'
        WHEN @crs<='藽' THEN 'qin'
        WHEN @crs<='櫦' THEN 'qing'
        WHEN @crs<='瓗' THEN 'qiong'
        WHEN @crs<='糗' THEN 'qiu'
        WHEN @crs<='覻' THEN 'qu'
        WHEN @crs<='勸' THEN 'quan'
        WHEN @crs<='礭' THEN 'que'
        WHEN @crs<='囕' THEN 'qun'
        WHEN @crs<='橪' THEN 'ran'
        WHEN @crs<='讓' THEN 'rang'
        WHEN @crs<='繞' THEN 'rao'
        WHEN @crs<='熱' THEN 're'
        WHEN @crs<='餁' THEN 'ren'
        WHEN @crs<='陾' THEN 'reng'
        WHEN @crs<='馹' THEN 'ri'
        WHEN @crs<='穃' THEN 'rong'
        WHEN @crs<='嶿' THEN 'rou'
        WHEN @crs<='擩' THEN 'ru'
        WHEN @crs<='礝' THEN 'ruan'
        WHEN @crs<='壡' THEN 'rui'
        WHEN @crs<='橍' THEN 'run'
        WHEN @crs<='鶸' THEN 'ruo'
        WHEN @crs<='栍' THEN 'sa'
        WHEN @crs<='虄' THEN 'sai'
        WHEN @crs<='閐' THEN 'san'
        WHEN @crs<='喪' THEN 'sang'
        WHEN @crs<='髞' THEN 'sao'
        WHEN @crs<='飋' THEN 'se'
        WHEN @crs<='篸' THEN 'sen'
        WHEN @crs<='縇' THEN 'seng'
        WHEN @crs<='霎' THEN 'sha'
        WHEN @crs<='曬' THEN 'shai'
        WHEN @crs<='鱔' THEN 'shan'
        WHEN @crs<='緔' THEN 'shang'
        WHEN @crs<='潲' THEN 'shao'
        WHEN @crs<='欇' THEN 'she'
        WHEN @crs<='瘮' THEN 'shen'
        WHEN @crs<='賸' THEN 'sheng'
        WHEN @crs<='瓧' THEN 'shi'
        WHEN @crs<='鏉' THEN 'shou'
        WHEN @crs<='虪' THEN 'shu'
        WHEN @crs<='誜' THEN 'shua'
        WHEN @crs<='卛' THEN 'shuai'
        WHEN @crs<='腨' THEN 'shuan'
        WHEN @crs<='灀' THEN 'shuang'
        WHEN @crs<='睡' THEN 'shui'
        WHEN @crs<='鬊' THEN 'shun'
        WHEN @crs<='鑠' THEN 'shuo'
        WHEN @crs<='乺' THEN 'si'
        WHEN @crs<='鎹' THEN 'song'
        WHEN @crs<='瘶' THEN 'sou'
        WHEN @crs<='鷫' THEN 'su'
        WHEN @crs<='算' THEN 'suan'
        WHEN @crs<='鐩' THEN 'sui'
        WHEN @crs<='潠' THEN 'sun'
        WHEN @crs<='蜶' THEN 'suo'
        WHEN @crs<='襨' THEN 'ta'
        WHEN @crs<='燤' THEN 'tai'
        WHEN @crs<='賧' THEN 'tan'
        WHEN @crs<='燙' THEN 'tang'
        WHEN @crs<='畓' THEN 'tao'
        WHEN @crs<='蟘' THEN 'te'
        WHEN @crs<='朰' THEN 'teng'
        WHEN @crs<='趯' THEN 'ti'
        WHEN @crs<='舚' THEN 'tian'
        WHEN @crs<='糶' THEN 'tiao'
        WHEN @crs<='餮' THEN 'tie'
        WHEN @crs<='乭' THEN 'ting'
        WHEN @crs<='憅' THEN 'tong'
        WHEN @crs<='透' THEN 'tou'
        WHEN @crs<='鵵' THEN 'tu'
        WHEN @crs<='褖' THEN 'tuan'
        WHEN @crs<='駾' THEN 'tui'
        WHEN @crs<='坉' THEN 'tun'
        WHEN @crs<='籜' THEN 'tuo'
        WHEN @crs<='韤' THEN 'wa'
        WHEN @crs<='顡' THEN 'wai'
        WHEN @crs<='贎' THEN 'wan'
        WHEN @crs<='朢' THEN 'wang'
        WHEN @crs<='躛' THEN 'wei'
        WHEN @crs<='璺' THEN 'wen'
        WHEN @crs<='齆' THEN 'weng'
        WHEN @crs<='齷' THEN 'wo'
        WHEN @crs<='鶩' THEN 'wu'
        WHEN @crs<='衋' THEN 'xi'
        WHEN @crs<='鏬' THEN 'xia'
        WHEN @crs<='鼸' THEN 'xian'
        WHEN @crs<='鱌' THEN 'xiang'
        WHEN @crs<='斆' THEN 'xiao'
        WHEN @crs<='躞' THEN 'xie'
        WHEN @crs<='釁' THEN 'xin'
        WHEN @crs<='臖' THEN 'xing'
        WHEN @crs<='敻' THEN 'xiong'
        WHEN @crs<='齅' THEN 'xiu'
        WHEN @crs<='蓿' THEN 'xu'
        WHEN @crs<='贙' THEN 'xuan'
        WHEN @crs<='瀥' THEN 'xue'
        WHEN @crs<='鑂' THEN 'xun'
        WHEN @crs<='齾' THEN 'ya'
        WHEN @crs<='灩' THEN 'yan'
        WHEN @crs<='樣' THEN 'yang'
        WHEN @crs<='鑰' THEN 'yao'
        WHEN @crs<='岃' THEN 'ye'
        WHEN @crs<='齸' THEN 'yi'
        WHEN @crs<='檼' THEN 'yin'
        WHEN @crs<='譍' THEN 'ying'
        WHEN @crs<='喲' THEN 'yo'
        WHEN @crs<='醟' THEN 'yong'
        WHEN @crs<='鼬' THEN 'you'
        WHEN @crs<='爩' THEN 'yu'
        WHEN @crs<='願' THEN 'yuan'
        WHEN @crs<='鸙' THEN 'yue'
        WHEN @crs<='韻' THEN 'yun'
        WHEN @crs<='雥' THEN 'za'
        WHEN @crs<='縡' THEN 'zai'
        WHEN @crs<='饡' THEN 'zan'
        WHEN @crs<='臟' THEN 'zang'
        WHEN @crs<='竈' THEN 'zao'
        WHEN @crs<='稄' THEN 'ze'
        WHEN @crs<='鱡' THEN 'zei'
        WHEN @crs<='囎' THEN 'zen'
        WHEN @crs<='贈' THEN 'zeng'
        WHEN @crs<='醡' THEN 'zha'
        WHEN @crs<='瘵' THEN 'zhai'
        WHEN @crs<='驏' THEN 'zhan'
        WHEN @crs<='瞕' THEN 'zhang'
        WHEN @crs<='羄' THEN 'zhao'
        WHEN @crs<='鷓' THEN 'zhe'
        WHEN @crs<='黮' THEN 'zhen'
        WHEN @crs<='證' THEN 'zheng'
        WHEN @crs<='豒' THEN 'zhi'
        WHEN @crs<='諥' THEN 'zhong'
        WHEN @crs<='驟' THEN 'zhou'
        WHEN @crs<='鑄' THEN 'zhu'
        WHEN @crs<='爪' THEN 'zhua'
        WHEN @crs<='跩' THEN 'zhuai'
        WHEN @crs<='籑' THEN 'zhuan'
        WHEN @crs<='戅' THEN 'zhuang'
        WHEN @crs<='鑆' THEN 'zhui'
        WHEN @crs<='稕' THEN 'zhun'
        WHEN @crs<='籱' THEN 'zhuo'
        WHEN @crs<='漬' THEN 'zi'
        WHEN @crs<='縱' THEN 'zong'
        WHEN @crs<='媰' THEN 'zou'
        WHEN @crs<='謯' THEN 'zu'
        WHEN @crs<='攥' THEN 'zuan'
        WHEN @crs<='欈' THEN 'zui'
        WHEN @crs<='銌' THEN 'zun'
        WHEN @crs<='咗' THEN 'zuo'
        --else  @crs end+' '+@re,@strlen=@strlen-1 
        --去掉拼音之间的间隔
        ELSE  @crs END+''+@re,@strlen=@strlen-1 
   END
 RETURN(@re)
END
GO

发表评论

邮箱地址不会被公开。 必填项已用*标注