在 SQL SERVER 中将汉字转换为拼音 - 根据拼音搜索汉字
在项目中,有时需要根据拼音来搜索对应的汉字,找了许多资料,也参考了一些示例,这个sql函数总是解决了这个问题,而且性能是找的资料中最好的。
原理是通过这个函数将中文汉字的字段中的数据转换成拼音,再根据拼音来进行搜索。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <lisky.li>
-- Create date: <2013>
-- Description: <汉字转全拼音函数>
-- =============================================
create function [dbo].[Get_QuanPin_li](@str varchar(120))
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 '
上一篇: 检测 SQL SERVER 数据库 CPU 瓶颈和内存瓶颈
下一篇: 常见网络安全说明