According to the Chinese characters, to find relevant records in the table, how to achieve it? For example, enter "ZGYH" to find all the "Bank of China" record. IF exists (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [f_getpy]') and xtype in (n'fn ', n'if ", n')) DROP FUNCTION [DBO] [f_getpy] Go
- Create function takes phonetic create function f_GetPy (@Str nvarchar (400)) returns nvarchar (4000) asbegin declare @strlen int, @ re nvarchar (4000) declare @t table (chr nchar (1) collate Chinese_PRC_CI_AS, letter nchar ( 1)) Insert @t select '', 'a' union all select 'eight', 'b' union all select '嚓', 'c' union all select '咑', 'D' union all succ '妸' , 'E' union all select ',' f 'union all select' 旮 ',' g 'union all select' ',' h 'union all select' 丌 ',' J 'Union All Select' 丌 ' , 'K' Union All SELECT 'FD', 'L' Union All Select '呒', 'M' Union All Select 'N,' N 'Union All Select' 噢 ',' O 'Union All SELECT' 妑 ' , 'P' Union All Select '7', 'Q' Union All Select '呥', 'R' Union All Select '仨', 'S' Union All Select 'He', 'T' Union All SELECT '屲' , 'W' Union All Select 'Xun', 'X' Union All Select '丫', 'Y' Union All SELECT '帀', 'Z'
SELECT @ Strlen = LEN (@str), @ re = '' while @strlen> 0 Begin select top 1 @ RE = Letter @ RE, @ Strlen = @ Strlen-1 from @ta 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) Endgo - Test SELECT DBO.F_GETPY ('Dongguan') AS Dongguan City, dbo.f_getpy ('AB Class C ") AS Chinese - When inquiry, you can call the above function to realize Chinese characters. Fuzzy query
SELECT * FROM table Where dbo.f_getpy (field) = 'zgyh'
