求SQL获取拼音首字母函数

fusoft 2007-09-20 11:46:30
1,汉字返回大写拼音首字母
2, 英文返回大写首字母
3,数字返回第一个数字
4,其他文字及符号返回“*”号
...全文
985 17 点赞 打赏 收藏 举报
写回复
17 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wookuang003 2011-10-29
收藏并学习
  • 打赏
  • 举报
回复
kakadragon 2008-09-05
liuhongyansn的解决方法很好,虽然还是有部分汉字没有包含。
  • 打赏
  • 举报
回复
老牛1 2008-03-14
这个只能处理一级字库的汉字遇到二级字库就不行了
正确的做法如下:


create function get_spell_1 (@str varchar(2))
returns varchar(1)
as
begin
declare @codedata varchar (8000)
set @codedata = 'CJWGNSPGCENEGYPBTWXZDXYKYGTPJNMJQMBSGZSCYJSYYFPGGBZGYDYWJKGALJSWKBJQHYJWPDZLSGMRYBYWWCCGZNKYDGTTNGJEYEKZYDCJNMCYLQLYPYQBQRPZSLWBDGKJFYXJWCLTBNCXJJJJCXDTQSQZYCDXXHGCKBPHFFSSPYBGMXJBBYGLBHLSSMZMPJHSOJNGHDZCDKLGJHSGQZHXQGKEZZWYMCSCJNYETXADZPMDSSMZJJQJYZCJJFWQJBDZBJGDNZCBWHGXHQKMWFBPBQDTJJZKQHYLCGXFPTYJYYZPSJLFCHMQSHGMMXSXJPKDCMBBQBEFSJWHWWGCKPYLQBGLDLCCTNMAEDDKSJNGKCSGXLHZAYBDBTSDKDYLHGYMYLCXPYCJNDQJWXQXFYYFJLEJBZRWCCQHQCSBZKYMGPLBMCRQCFLNYMYQMSQTRBCJTHZTQFRXCHXMCJCJLXQGJMSHZKBSWXEMDLCKFSYDSGLYCJJSSJNQBJCTYHBFTDCYJDGWYGHQFRXWCKQKXEBPDJPXJQSRMEBWGJLBJSLYYSMDXLCLQKXLHTJRJJMBJHXHWYWCBHTRXXGLHJHFBMGYKLDYXZPPLGGPMTCBBAJJZYLJTYANJGBJFLQGDZYQCAXBKCLECJSZNSLYZHLXLZCGHBXZHZNYTDSBCJKDLZAYFFYDLABBGQSZKGGLDNDNYSKJSHDLXXBCGHXYGGDJMMZNGMMCCGWZSZXSJBZNMLZDTHCQYDBDLLSCDDNLKJYHJSYCJLKOHQASDHNHCSGAEHDAASHTCPLCPQYBSDMPJLPCJAQLCDHJJASPRCHNGJNLHLYYQYHWZPNCCGWWMZFFJQQQQXXACLBHKDJXDGMMYDJXZLLSYGXGKJRYWZWYCLZMCSJZLDBNDCFCXYHLSCHYCJQPPQAGMNYXPFRKSSBJLYXYJJGLNSCMHCWWMNZJJLHMHCHSYPPTTXRYCSXBYHCSMXJSXNBWGPXXTAYBGAJCXLYPDCCWQOCWKCCSBNHCPDYZNBCYYTYCKSKYBSQKKYTQQXFCWCHCWKELCQBSQYJQCCLMTHSYWHMKTLKJLYCHWHEQJHTJHPPQPQSCFYMMCMGBMHGLGSLLYSDLLLJPCHMJHWLJCYHZJXHDXJLHXRSWLWZJCBXMHZQXSDZPMGFCSGLSDYMJSHXPJXOMYQKNMYBLRTHBCFTPMGYXLCHLHLZYLXGSSSSCCLSLDCLEPBHSHXYYFHBMGDFYCNJQWLQHJJCYWJZTEJJDHFBLQXTQKWHDCHQXAGTLXLJXMSLJHDZKZJECXJCJNMBBJCSFYWKBJZGHYSDCPQYRSLJPCLPWXSDWEJBJCBCNAYTMGMBAPCLYQBCLZXCBNMSGGFNZJJBZSFQYNDXHPCQKZCZWALSBCCJXPOZGWKYBSGXFCFCDKHJBSTLQFSGDSLQWZKXTMHSBGZHJCRGLYJBPMLJSXLCJQQHZMJCZYDJWBMJKLDDPMJEGXYHYLXHLQYQHKYCWCJMYHXNATJHYCCXZPCQLBZWWWTWBQCMLBMYNJCCCXBBSNZZLJPLJXYZTZLGCLDCKLYRZZGQTGJHHGJLJAXFGFJZSLCFDQZLCLGJDJCSNCLLJPJQDCCLCJXMYZFTSXGCGSBRZXJQQCCZHGYJDJQQLZXJYLDLBCYAMCSTYLBDJBYREGKLZDZHLDSZCHZNWCZCLLWJQJJJKDGJCOLBBZPPGLGHTGZCYGEZMYCNQCYCYHBHGXKAMTXYXNBSKYZZGJZLQJDFCJXDYGJQJJPMGWGJJJPKJSBGBMMCJSSCLPQPDXCDYYKYPCJDDYYGYWCHJRTGCNYQLDKLJCZZGZCCJGDYKSGPZMDLCPHNJAFYZDJCNMWESCSGLBTZCGMSDLLYXQSXSBLJSBBSGGHFJLWPMZJNLYYWDQSHZXTYYWHMCYHYWDBXBTLMSWYYFSBJCBDXXLHJHFPSXZQHFZMQCZTQCXZXRDKDJHNNYZQQFNQDMMGNYDXMJGDHCDYCBFFALLZTDLTFKMXQZDNGEQDBDCZJDXBZGSQQDDJCMBKXFFXMKDMCSYCHZCMLJDJYNHPRSJMKMPCKLGDBQTFZSWTFGGLYPLLJZHGJJGYPZLTCSMCNBTJBHFKDHBYZGKPBBYMTDLSXSBNPDKLEYCJNYCDYKZDDHQGSDZSCTARLLTKZLGECLLKJLJJAQNBDGGGHFJTZQJSECSHALQFMMGJNLYJBBTMLYCXDCJPLDLPCQDHSYCBZSCKBZMSLJFLHRBJSNBRGJHXPDGDJYBZGDLGCSEZGXLBLGYXTWMABCHECMWYJYZLLJJSHLGNDJLSLYGKDZPZXJYYZLPCXSZFGWYYDLYHCLJSCMBJHBLYJLYCBLYDPDQYSXKTBYTDKDXJYPCNRJMFDJGKLCCJBCTBJDDBBLBLCDQRPPXJCGLZCSHLTOLJNMDDDLNGKAQAKGJGYHHEZNMSHRPHQQJCHGMFPRXCJGDYCHGHLYRZQLCNGJNZSQDKQJYMSZSWLCFQJQXGBGGXMDJWLMCRNFKKFSYYLJBMQAMMMYCCTBSHCPTXXZZSMPHFSHMCLMLDJFYQXSDYJDJJZZHQPDSZGLSSJBCKBXYQZJSGPSXJZQZNQTBDKWXJKHHGFLBCSMDLDGDZDBLZKYCQNNCSYBZBFGLZZXSWMSCCMQNJQSBDQSJTXXMBLDXCCLZSHZCXRQJGJYLXZFJPHYMZQQYDFQJJLCZNZJCDGZYGCDXMZYSCTLKPHTXHTLBJXJLXSCDQCCBBQJFQZFSLTJBTKQBSXJJLJCHCZDBZJDCZJCCPRNLQCGPFCZLCLCXZDMXMPHGSGZGSZZQJXLWTJPFSYASLCJBTCKWCWMYTCSJJLJCQLWZMALBXYFBPNLSCHTGJWEJJXXGLLJSTGSHJQLZFKCGNNDSZFDEQFHBSAQDGYLBXMMYGSZLDYDJMJJRGBJGKGDHGKBLGKBDMBYLXWCXYTTYBKMRJJZXQJBHLMHMJJZMQASLDCYXYQDLQCAFYWYXQHZ'
declare @gbcode int
--获得一个汉字的首字母 liuhy email:liuhongyansn@163.com
declare @l int
declare @r int

set @l = ASCII(@str) - 160

set @r = cast((substring(CAST(@str as varbinary), 2,1)) as int) - 160

if(@l <= 0 or @r <= 0)begin
return @str
end

set @gbcode = @l * 100 + @r

declare @spell varchar(1)
if (@gbcode >= 1601 and @gbcode < 1637) begin
set @spell = 'A'
end else if (@gbcode >= 1637 and @gbcode < 1833)
set @spell = 'B'
else if (@gbcode >= 1833 and @gbcode < 2078)
set @spell = 'C'
else if (@gbcode >= 2078 and @gbcode < 2274)
set @spell = 'D'
else if (@gbcode >= 2274 and @gbcode < 2302)
set @spell = 'E'
else if (@gbcode >= 2302 and @gbcode < 2433)
set @spell = 'F'
else if (@gbcode >= 2433 and @gbcode < 2594)
set @spell = 'G'
else if (@gbcode >= 2594 and @gbcode < 2787)
set @spell = 'H'
else if (@gbcode >= 2787 and @gbcode < 3106)
set @spell = 'J'
else if (@gbcode >= 3106 and @gbcode < 3212)
set @spell = 'K'
else if (@gbcode >= 3212 and @gbcode < 3472)
set @spell = 'L'
else if (@gbcode >= 3472 and @gbcode < 3635)
set @spell = 'M'
else if (@gbcode >= 3635 and @gbcode < 3722)
set @spell = 'N'
else if (@gbcode >= 3722 and @gbcode < 3730)
set @spell = 'O'
else if (@gbcode >= 3730 and @gbcode < 3858)
set @spell = 'P'
else if (@gbcode >= 3858 and @gbcode < 4027)
set @spell = 'Q'
else if (@gbcode >= 4027 and @gbcode < 4086)
set @spell = 'R'
else if (@gbcode >= 4086 and @gbcode < 4390)
set @spell = 'S'
else if (@gbcode >= 4390 and @gbcode < 4558)
set @spell = 'T'
else if (@gbcode >= 4558 and @gbcode < 4684)
set @spell = 'W'
else if (@gbcode >= 4684 and @gbcode < 4925)
set @spell = 'X'
else if (@gbcode >= 4925 and @gbcode < 5249)
set @spell = 'Y'
else if (@gbcode >= 5249 and @gbcode <= 5589)
set @spell = 'Z'
else if (@gbcode >= 5601 and @gbcode <= 8794) begin
declare @i int
set @i = (@l - 56) * 94 + @r
set @spell = substring(@codedata, @i, 1)
end
else
begin

set @spell = @str
end


return(@spell)
end

go


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






ALTER function get_spell (@str varchar(512))
returns varchar(512)
as
begin
declare @rs varchar(512)
set @rs = ''
--获得汉字的首字母
--liuhy
declare @strlen int

select @strlen = len(@str)

while @strlen>0
begin
select @rs = dbo.get_spell_1(substring(@str, @strlen, 1)) + @rs
set @strlen = @strlen-1
end
return (@rs)
end






GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


  • 打赏
  • 举报
回复
hbjlwhl 2007-09-22
强人啊!!
  • 打赏
  • 举报
回复
烤火的鱼 2007-09-21
我是新建了一个表,从全拼码表中将所有汉字首字母取出来放进去,直接查询就行了,目前碰到的问题是多音字处理,只能使用默认的了,如果一定要自动,可以考虑用多音词组协助解决
  • 打赏
  • 举报
回复
sp4 2007-09-20
上面这个过滤是根据特定的来做的,至于LZ的过滤自己来填加即可
  • 打赏
  • 举报
回复
sp4 2007-09-20
--获取字符串拼音字头
--Pqs 2006-11-20
--@Char 输入的字符串
CREATE function dbo.GetPY (@Char varchar(100))
returns varchar(100) as
begin
--字符串长度 返回数据 遍历字符串位置 遍历的字符
declare @i_Len int, @c_Return varchar(100), @i_pos int,@c varchar(2),@c_CN varchar(100)
--初始化数据
--set @str='龙岂池中物'
set @i_Len = len(@Char)
set @c_Return = ''
set @i_pos = 0
--遍历字符串
while (@i_pos < @i_Len )
begin
set @i_pos = @i_pos + 1
set @c_CN = substring(@Char, @i_pos, 1)
if (@c_CN > 'z' )
--中文处理,查询匹配
begin
set @c = case when @c_CN>='帀' then 'Z' when @c_CN>='丫' and @c_CN<'帀' then 'Y' when @c_CN>='夕' and @c_CN<'丫' then 'X' when @c_CN>='屲' and @c_CN<'夕' then 'W'
when @c_CN>='他' and @c_CN<'屲' then 'T' when @c_CN>='仨' and @c_CN<'他' then 'S' when @c_CN>='呥' and @c_CN<'仨' then 'R' when @c_CN>='七' and @c_CN<'呥' then 'Q'
when @c_CN>='妑' and @c_CN<'七' then 'P' when @c_CN>='噢' and @c_CN<'妑' then 'O' when @c_CN>='拏' and @c_CN<'噢' then 'N' when @c_CN>='嘸' and @c_CN<'拏' then 'M'
when @c_CN>='垃' and @c_CN<'嘸' then 'L' when @c_CN>='咔' and @c_CN<'垃' then 'K' when @c_CN>='丌' and @c_CN<'咔' then 'J' when @c_CN>='铪' and @c_CN<'丌' then 'H'
when @c_CN>='旮' and @c_CN<'铪' then 'G' when @c_CN>='发' and @c_CN<'旮' then 'F' when @c_CN>='妸' and @c_CN<'发' then 'E' when @c_CN>='咑' and @c_CN<'妸' then 'D'
when @c_CN>='嚓' and @c_CN<'咑' then 'C' when @c_CN>='八' and @c_CN<'嚓' then 'B' when @c_CN>='吖' and @c_CN<'八' then 'A' Else '' End
-- select top 1 @c = Code from Code_PY where name <= @c_CN
end
else
begin
--过滤字符(除了字符、数字和'.'外的所有字符)
if (@c_CN>='a' or (@c_CN>='0' and @c_CN<='9') or @c_CN='.')
set @c=@c_CN
else
set @c=''
end

set @c_Return=@c_Return+isnull(@c ,'')
end
return upper(@c_Return)
end

  • 打赏
  • 举报
回复
fusoft 2007-09-20
哎,GOOGLE我也会用的啊,
1,汉字返回大写拼音首字母
2, 英文返回大写首字母
3,数字返回第一个数字
4,其他文字及符号返回“*”号
这些情况怎么处理呢,我没写过SQL自定义函数,菜鸟啊
  • 打赏
  • 举报
回复
dawugui 2007-09-20
最后面写了调用方法:

--调用
select * from a order by case Name when '其他' then 1 else 0 end,dbo.f_GetPy(Name)
  • 打赏
  • 举报
回复
dawugui 2007-09-20
--1. 按拼音排序
DECLARE @t TABLE(col varchar(2))
INSERT @t SELECT '中'
UNION ALL SELECT '国'
UNION ALL SELECT '人'

SELECT * FROM @t ORDER BY col COLLATE Chinese_PRC_CS_AS_KS_WS
/*--结果
col
----



--*/
GO


/*==========================================*/


--2. 汉字首字母查询处理用户定义函数
CREATE FUNCTION f_GetPY(@str nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @py TABLE(
ch char(1),
hz1 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS,
hz2 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS)
INSERT @py SELECT 'A',N'吖',N'鏊'
UNION ALL SELECT 'B',N'八',N'簿'
UNION ALL SELECT 'C',N'嚓',N'错'
UNION ALL SELECT 'D',N'哒',N'跺'
UNION ALL SELECT 'E',N'屙',N'贰'
UNION ALL SELECT 'F',N'发',N'馥'
UNION ALL SELECT 'G',N'旮',N'过'
UNION ALL SELECT 'H',N'铪',N'蠖'
UNION ALL SELECT 'J',N'丌',N'竣'
UNION ALL SELECT 'K',N'咔',N'廓'
UNION ALL SELECT 'L',N'垃',N'雒'
UNION ALL SELECT 'M',N'妈',N'穆'
UNION ALL SELECT 'N',N'拿',N'糯'
UNION ALL SELECT 'O',N'噢',N'沤'
UNION ALL SELECT 'P',N'趴',N'曝'
UNION ALL SELECT 'Q',N'七',N'群'
UNION ALL SELECT 'R',N'蚺',N'箬'
UNION ALL SELECT 'S',N'仨',N'锁'
UNION ALL SELECT 'T',N'他',N'箨'
UNION ALL SELECT 'W',N'哇',N'鋈'
UNION ALL SELECT 'X',N'夕',N'蕈'
UNION ALL SELECT 'Y',N'丫',N'蕴'
UNION ALL SELECT 'Z',N'匝',N'做'
DECLARE @i int
SET @i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)
WHILE @i>0
SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1),ch)
,@i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)
FROM @py
WHERE SUBSTRING(@str,@i,1) BETWEEN hz1 AND hz2
RETURN(@str)
END
GO
  • 打赏
  • 举报
回复
fusoft 2007-09-20
大哥啊!这个函数我已经在网上找到了,就要不知道怎么处理其他情况。。。。才来发帖子!
  • 打赏
  • 举报
回复
livan1038 2007-09-20
牛人,學習一下
  • 打赏
  • 举报
回复
andy84 2007-09-20
猛人啊
  • 打赏
  • 举报
回复
子陌红尘 2007-09-20
其他情况自己处理一下。
  • 打赏
  • 举报
回复
子陌红尘 2007-09-20
create function f_GetPy(@str nvarchar(4000))
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 into @t(chr,letter)
select '吖','A' union all select '八','B' union all
select '嚓','C' union all select '咑','D' union all
select '妸','E' union all select '发','F' union all
select '旮','G' union all select '铪','H' union all
select '丌','J' union all select '咔','K' union all
select '垃','L' union all select '嘸','M' union all
select '拏','N' union all select '噢','O' union all
select '妑','P' union all select '七','Q' union all
select '呥','R' union all select '仨','S' union all
select '他','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+@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
--调用
select * from a order by case Name when '其他' then 1 else 0 end,dbo.f_GetPy(Name)
  • 打赏
  • 举报
回复
turenjie 2007-09-20
收藏好贴
  • 打赏
  • 举报
回复
wgzaaa 2007-09-20
1,汉字返回大写拼音首字母
2, 英文返回大写首字母
3,数字返回第一个数字
4,其他文字及符号返回“*”号
还不是很确定,不妨列举些数据说明
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2007-09-20 11:46
社区公告
暂无公告