SQL functions used by MSSQL to parse personal names into pinyin

 

1. The first function is to obtain the first letter of each Chinese Character in the name.

Create Function f_getpy (@ STR nvarchar (4000 ))
Returns nvarchar (4000)
As
Begin
Declare @ py table (
Ch char (1 ),
Hz1 nchar (1) Collate chinese_prc_cs_as_ks_ws,
Hz2 nchar (1) Collate chinese_prc_cs_as_ks_ws)
Insert @ py select 'A', n' A ', n' else'
Union all select 'B', n'8', n''
Union all select 'C', n'hangzhou', n'error'
Union all select 'D', N 'D', N 'region'
Union all select 'E', N 'hour', N 'er'
Union all select 'F', N 'F', N 'hangzhou'
Union all select 'G', n'hangzhou', n' over'
Union all select 'h', N 'haid', N 'hangzhou'
Union all select 'J', n' ', n'hangzhou'
Union all select 'k', N 'hangzhou', N 'quar'
Union all select 'l', n'hangzhou', n'hangzhou'
Union all select 'M', N 'M', N 'mu'
Union all select 'n', n' take ', N 'none'
Union all select 'O', n'ou', n' else'
Union all select 'P', n' lie ', n' exposure'
Union all select 'Q', N '7', N 'group'
Union all select 'R', N 'hangzhou', N 'hangzhou'
Union all select's ', N 'locks', N 'lock'
Union all select 'T', n' he ', n' else'
Union all select 'w', n'wa', n'hangzhou'
Union all select 'x', N 'Xi ', N 'taobao'
Union all select 'y', n' ya ', n' Yun'
Union all select 'Z', N 'Z', N 'do'
Declare @ I int
Set @ I = patindex ('% [Do-Do] %' collate chinese_prc_cs_as_ks_ws, @ Str)
While @ I> 0
Select @ STR = Replace (@ STR, substring (@ STR, @ I, 1), ch)
, @ I = patindex ('% [Do-Do] %' collate chinese_prc_cs_as_ks_ws, @ Str)
From @ py
Where substring (@ STR, @ I, 1) between Hz1 and hz2
Return (@ Str)
End
Go

-- Usage
Select DBO. f_getpy ('wu Yiling ') as Dongguan City, DBO. f_getpy (' AB 中 C') as Chinese

-- Update a field in an existing table into a pinyin field.

Select name, Pinyin from person_main_test

Update person_main_test set pinyin = DBO. f_getpy (name)

 

 

 

2. Obtain the Pinyin of the entire Chinese name

Create Function f_getpyall (@ STR varchar (100 ))
Returns varchar (8000)
As
Begin
Declare @ returnvalue varchar (8000)
Declare @ Re table (ID int, re varchar (8000 ))

Declare @ I int, @ ilen int, @ splitchr varchar (1)
Select @ splitchr = '', @ I = 1, @ ilen = Len (@ Str)
Insert into @ Re select @ I, Py from yingshe where CHR = substring (@ STR, @ I, 1)
While @ I <@ ilen
Begin
Set @ I = @ I + 1
Insert into @ Re select @ I, RE + @ splitchr + py from @ Re A, yingshe B
Where a. ID = @ I-1 and B. CHR = substring (@ STR, @ I, 1)
End
Select @ returnvalue = Re from @ Re where id = @ I
Return (@ returnvalue)
End