Create a user-defined phonetic function in sql:
Create function f_getpy (@Str nvarchar)
returns nvarchar (4000)
as
begin
declare @strlen int, @re nvarchar (4000)
Declare @t table (CHR nchar (1) Collate chinese_prc_ci_as,letter nchar (1))
I Nsert @t Select ' Acridine ', ' A ' union ALL select ' Eight ', ' B '
Union ALL Select ' Cha ', ' C ' union ALL Select ' 咑 ', ' D '
UNION ALL Selec T ' 妸 ', ' E ' union ALL Select ' Hair ', ' F '
UNION ALL Select ' There ', ' G ' union ALL Select ' Hafnium ', ' H '
UNION ALL Select ' No ', ' J ' Unio n all select ' ka ', ' K '
Union ALL Select ' garbage ', ' L ' union ALL Select ' 嘸 ', ' M '
Union ALL Select ' 拏 ', ' N ' union ALL SELECT ' ' Oh ', ' O '
Union ALL Select ' 妑 ', ' P ' union ALL select ' Seven ', ' Q '
Union ALL Select ' 呥 ', ' R ' union ALL SELECT ' sa ', ' S '
UN Ion all Select ' he ', ' T ' union ALL Select ' 屲 ', ' W '
UNION ALL Select ' XI ', ' X ' union ALL Select ' Ya ', ' Y '
UNION ALL Select ' 帀 ', ' Z '
Select @strlen =len (@str), @re = '
While @strlen >0
Begin
Select top 1 @re =letter+ @re, @strlen = @strlen-1
From @t a where chr<=substring (@str, @strlen, 1)
ORDER BY CHR DESC
If @ @rowcount =0
Select @re =substring (@str, @strlen, 1) + @re, @strlen = @strlen-1
End
Return (@re)
End
Go
--Test
Select Dbo.f_getpy (' Dongguan ') as Dongguan, dbo.f_getpy (' C people of AB ') as Chinese
--After the query, you can call the above function to realize the Chinese character fuzzy query
SELECT * FROM table where dbo.f_getpy (field) = ' Zgyh '