SQL语句提取出中文的拼音首字母 sql 语句返回中文姓名的首字母缩写
来源:互联网 发布:java三种设计模式 编辑:程序博客网 时间:2024/04/30 04:27
--将中文字符串转化成文字首拼音的组合
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
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
--函数调用实例:
select dbo.fun_getPY('中华人民共和国AAA01')
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ZHRMGHGAAA01
(1 行受影响)
*/
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
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
--函数调用实例:
select dbo.fun_getPY('中华人民共和国AAA01')
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ZHRMGHGAAA01
(1 行受影响)
*/
0 0
- SQL语句提取出中文的拼音首字母 sql 语句返回中文姓名的首字母缩写
- SQL语句提取出中文的拼音首字母
- 获取中文拼音首字母的SQL函数
- SQL 中文转换拼音首字母
- sql 中文转拼音首字母
- 多音字按拼音首字母排序sql语句的问题解决方案
- SQL 中文得到拼音大写首字母的方法
- 取中文的拼音首字母
- Java获取中文拼音、中文首字母缩写和中文首字母
- sql 语句 按汉字拼音首字母排序
- sql语句-按照汉字拼音首字母排序
- sql语句按照汉字拼音首字母排序
- sql语句按照汉字拼音首字母排序
- sql语句-按照汉字拼音首字母排序
- 匹配中文字符串的拼音首字母或英文字符串的首字母缩写
- 通过GBK编码提取中文汉字的拼音首字母
- COOKBOOK 6.7 提取姓名的大写首字母缩写
- java 获取中文拼音首字母(缩写)
- resource.h(10): fatal error RC1004: unexpected end of file found
- Trees on the level
- 怎样录制屏幕并将结果保存为Gif
- leetcode:Triangle 三角形
- android 制作9.png图片
- SQL语句提取出中文的拼音首字母 sql 语句返回中文姓名的首字母缩写
- Sicily 1153 马的周游问题[Special judge] && sicily 1152
- vfs读写流程梳理
- nyoj14会场安排问题
- UIWebView的简单介绍
- MySQL慢查询统计
- tolua++入门
- mem相关函数不断补充ing
- 动态flash图片焦点轮换