http://dev.9cbs.net/develop/Article/47/47155.SHTM According to the Chinese character, how 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 Pinyin Function Create Function F_Getpy (@str nvarchar (400)) 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)) INSERT @t select '', 'a' union all select '八', 'b' union all select '嚓', 'c' union all succ '咑', 'd' Union all select '妸', 'e' union all select ',' f 'union all select' 旮 ',' g 'union all select' ',' h 'union all succ', 'J' Union all select '', 'k' union all select '呒', 'L' Union All Select '呒', 'M' Union All Select 'Ni,' N 'Union All Select' 噢 ',' O ' Union all select '妑', 'p' union all select 'seven', 'q' union all select '呥', 'r' union all select '仨', 's' union all select 'he', 't' Union all select '屲', 'W' Union All Select '', 'x' union all select '丫', 'y' union all select '帀', 'Z' SELECT @ strlen = len (@str), @ RE = '' While @strlen> 0 Begin SELECT TOP 1 @ RE = Letter @R, @ 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 (@
