sql server如何获取中文首字母函数
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">sql server获取中文首字母函数</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">利用Unicode编码</a></li><li><a href="#_lab2_0_1">利用GBK</a></li></ul><li><a href="#_label1">总结</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>sql server获取中文首字母函数</h2><p>java的同学可以了解下pinyin4j，js可以了解ChinesePY.js</p>
<p>获取拼音的意义多在于需对中文进行排序，例如：通讯录。</p>
<p><strong>获取汉字首字母的解决方案有</strong></p>
<ul><li>利用CJK统一表意符</li><li>利用GBK</li><li>利用Unicode编码</li></ul>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>利用Unicode编码</h3>
<p><strong>第二个方法不太好用有的字获取不出来，这个比较顶</strong></p>
<div class="jb51code"><pre class="brush:sql;">ALTER function .(@str varchar(500))
returns varchar(500)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'a' as PY,N'骜' as word
union all select 'b',N'簿'
union all select 'c',N'错'
union all select 'd',N'鵽'
union all select 'e',N'樲'
union all select 'f',N'鳆'
union all select 'g',N'腂'
union all select 'h',N'夻'
union all select 'j',N'攈'
union all select 'k',N'穒'
union all select 'l',N'鱳'
union all select 'm',N'旀'
union all select 'n',N'桛'
union all select 'o',N'沤'
union all select 'p',N'曝'
union all select 'q',N'囕'
union all select 'r',N'鶸'
union all select 's',N'蜶'
union all select 't',N'箨'
union all select 'w',N'鹜'
union all select 'x',N'鑂'
union all select 'y',N'韵'
union all select 'z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end</pre></div>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>利用GBK</h3>
<p><strong>把@length=1改成@length=1len(@str) ，效果从输入首字的首字母变成输出全部字的首字母</strong></p>
<div class="jb51code"><pre class="brush:sql;">create function .(@str varchar(500))
returns varchar(500)
as
begin
declare @cyc int,@length int,@str1 varchar(100),@charcate varbinary(20)
set @cyc=1--从第几个字开始取
set @length=1--len(@str)--输入汉字的长度 只需要返回首字母
set @str1=''--用于存放返回值
while @cyc<=@length
begin
select @charcate=cast(substring(@str,@cyc,1) as varbinary)--每次取出一个字并将其转变成二进制,便于与GBK编码表进行比较
if @str LIKE '%' --英文开头直接返回
return @str
if @charcate>=0XB0A1 and @charcate<=0XB0C4
set @str1=@str1+'a'--说明此汉字的首字母为A,以下同上
else if @charcate>=0XB0C5 and @charcate<=0XB2C0
set @str1=@str1+'b'
else if @charcate>=0XB2C1 and @charcate<=0XB4ED
set @str1=@str1+'c'
else if @charcate>=0XB4EE and @charcate<=0XB6E9
set @str1=@str1+'d'
else if @charcate>=0XB6EA and @charcate<=0XB7A1
set @str1=@str1+'e'
else if @charcate>=0XB7A2 and @charcate<=0XB8C0
set @str1=@str1+'f'
else if @charcate>=0XB8C1 and @charcate<=0XB9FD
set @str1=@str1+'g'
else if @charcate>=0XB9FE and @charcate<=0XBBF6
set @str1=@str1+'h'
else if @charcate>=0XBBF7 and @charcate<=0XBFA5
set @str1=@str1+'i'
else if @charcate>=0XBFA6 and @charcate<=0XC0AB
set @str1=@str1+'j'
else if @charcate>=0XC0AC and @charcate<=0XC2E7
set @str1=@str1+'k'
else if @charcate>=0XC2E8 and @charcate<=0XC4C2
set @str1=@str1+'l'
else if @charcate>=0XC2E8 and @charcate<=0XC4C2
set @str1=@str1+'m'
else if @charcate>=0XC4C3 and @charcate<=0XC5B5
set @str1=@str1+'n'
else if @charcate>=0XC5B6 and @charcate<=0XC5BD
set @str1=@str1+'o'
else if @charcate>=0XC5BE and @charcate<=0XC6D9
set @str1=@str1+'p'
else if @charcate>=0XC6DA and @charcate<=0XC8BA
set @str1=@str1+'q'
else if @charcate>=0XC8BB and @charcate<=0XC8F5
set @str1=@str1+'r'
else if @charcate>=0XC8F6 and @charcate<=0XCBF9
set @str1=@str1+'s'
else if @charcate>=0XCBFA and @charcate<=0XCDD9
set @str1=@str1+'t'
else if @charcate>=0XCDDA and @charcate<=0XCEF3
set @str1=@str1+'w'
else if @charcate>=0XCEF4 and @charcate<=0XD1B8
set @str1=@str1+'x'
else if @charcate>=0XD1B9 and @charcate<=0XD4D0
set @str1=@str1+'y'
else if @charcate>=0XD4D1 and @charcate<=0XD7F9
set @str1=@str1+'z'
set @cyc=@cyc+1--取出输入汉字的下一个字
end
return @str1--返回输入汉字的首字母
end</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>总结</h2>
<p>以上为个人经验，希望能给大家一个参考，也希望大家多多支持琼殿技术社区。</p>
頁:
[1]