大哥们好！遇到一急问题。SQL 表字段、内容如下。id name value
1 张三 szhang
2 李四 sli
3 王麻子 mzwang
现要根据name 获得 value 的值。
比如，张三，获取他名字的拼音应该为 sZhang，姓氏拼音必须完整，名字取第一个字母，大小写无所谓。
王麻子，获取得到mzWang。如上，忘各位大哥们赐教。
1 张三 szhang
2 李四 sli
3 王麻子 mzwang
现要根据name 获得 value 的值。
比如，张三，获取他名字的拼音应该为 sZhang，姓氏拼音必须完整，名字取第一个字母，大小写无所谓。
王麻子，获取得到mzWang。如上，忘各位大哥们赐教。
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
declare @t table(
col nvarchar(1))
insert @t
select '中'
union all select '国'
union all select '人'select * from @t
order by col collate chinese_prc_cs_as_ks_ws
--得到汉字助记码
----a.生成汉字列表(GB2312)
select top 71
ID=IDENTITY(int,176,1) into #a
from dbo.syscolumns a,dbo.syscolumns
select top 94
id=identity(int,161,1) into #b
from dbo.syscolumns a,dbo.syscolumns
select a.id,b.id,cast(a.id as binary(1)),cast(b.id as binary(1)),
ch=cast(
cast(a.id as BINARY(1))+cast(b.id as BINARY(1)) as CHAR(2)
) collate chinese_prc_cs_as_ks_ws
from #a a,#b b
where not(a.id=215 and b.id between 250 and 254)
order by ch
create table py_zjm(zjm nchar(1) primary key,zjm1 nchar(1) collate chinese_prc_cs_as_ks_ws,
zjm2 nchar(1) collate chinese_prc_cs_as_ks_ws)
create table py_zjm(zjm nchar(1) primary key,zjm1 nchar(1),
zjm2 nchar(1)) --DROP TABLE PY_ZJM
insert py_zjm
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'做'-- SELECT * FROM PY_ZJM
--得到助记码的函数
ALTER function dbo.f_dbo_zjm
(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
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),zjm),
--取下个要处理的汉字位置
@i=patindex('%[吖-做]%' collate chinese_prc_cs_as_ks_ws,@str)
from dbo.py_zjm
where substring(@str,@i,1) between zjm1 and zjm2
return(@str)
end
select dbo.f_dbo_zjm('微软MS SQL SERVER数$^&*(*(据库2009-09管理系统ORACLE')
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
获取得到的是汉字的首字母，并没有达到我想要的效果哦！
create table py_table (chr nchar(1) collate Chinese_PRC_CS_AS_KS_WS,py nvarchar(20))
insert into py_table select'吖','a'
insert into py_table select'厑','aes'
insert into py_table select'哎','ai'
insert into py_table select'安','an'
insert into py_table select'肮','ang'
insert into py_table select'凹','ao'
insert into py_table select'八','ba'
insert into py_table select'挀','bai'
insert into py_table select'兡','baike'
insert into py_table select'瓸','baiwa'
insert into py_table select'扳','ban'
insert into py_table select'邦','bang'
insert into py_table select'勹','bao'
insert into py_table select'萡','be'
insert into py_table select'陂','bei'
insert into py_table select'奔','ben'
insert into py_table select'伻','beng'
insert into py_table select'皀','bi'
insert into py_table select'边','bian'
insert into py_table select'辪','uu'
insert into py_table select'灬','biao'
insert into py_table select'憋','bie'
insert into py_table select'汃','bin'
insert into py_table select'冫','bing'
insert into py_table select'癶','bo'
insert into py_table select'峬','bu'
insert into py_table select'嚓','ca'
insert into py_table select'偲','cai'
insert into py_table select'乲','cal'
insert into py_table select'参','can'
insert into py_table select'仓','cang'
insert into py_table select'撡','cao'
insert into py_table select'冊','ce'
insert into py_table select'膥','cen'
insert into py_table select'噌','ceng'
insert into py_table select'硛','ceok'
insert into py_table select'岾','ceom'
insert into py_table select'猠','ceon'
insert into py_table select'乽','ceor'
insert into py_table select'叉','cha'
insert into py_table select'犲','chai'
insert into py_table select'辿','chan'
insert into py_table select'伥','chang'
insert into py_table select'抄','chao'
insert into py_table select'车','che'
insert into py_table select'抻','chen'
insert into py_table select'阷','cheng'
insert into py_table select'吃','chi'
insert into py_table select'充','chong'
insert into py_table select'抽','chou'
insert into py_table select'出','chu'
insert into py_table select'膗','chuai'
insert into py_table select'巛','chuan'
insert into py_table select'刅','chuang'
insert into py_table select'吹','chui'
insert into py_table select'旾','chun'
insert into py_table select'踔','chuo'
insert into py_table select'呲','ci'
insert into py_table select'嗭','cis'
insert into py_table select'从','cong'
insert into py_table select'凑','cou'
insert into py_table select'粗','cu'
insert into py_table select'汆','cuan'
insert into py_table select'崔','cui'
insert into py_table select'邨','cun'
insert into py_table select'瑳','cuo'
insert into py_table select'撮','chua'
insert into py_table select'咑','da'
insert into py_table select'呔','dai'
insert into py_table select'丹','dan'
insert into py_table select'当','dang'
insert into py_table select'刀','dao'
insert into py_table select'恴','de'
insert into py_table select'揼','dem'
insert into py_table select'扥','den'
insert into py_table select'灯','deng'
insert into py_table select'仾','di'
insert into py_table select'嗲','dia'
insert into py_table select'敁','dian'
insert into py_table select'刁','diao'
insert into py_table select'爹','die'
insert into py_table select'哋','dei'
insert into py_table select'嚸','dim'
insert into py_table select'丁','ding'
insert into py_table select'丟','diu'
insert into py_table select'东','dong'
insert into py_table select'吺','dou'
insert into py_table select'剢','du'
insert into py_table select'耑','duan'
insert into py_table select'叾','dug'
insert into py_table select'垖','dui'
insert into py_table select'吨','dun'
insert into py_table select'咄','duo'
insert into py_table select'妸','e'
insert into py_table select'奀','en'
insert into py_table select'鞥','eng'
insert into py_table select'仒','eo'
insert into py_table select'乻','eol'
insert into py_table select'旕','eos'
insert into py_table select'儿','er'
insert into py_table select'发','fa'
insert into py_table select'帆','fan'
insert into py_table select'匚','fang'
insert into py_table select'飞','fei'
insert into py_table select'吩','fen'
insert into py_table select'丰','feng'
insert into py_table select'瓰','fenwa'
insert into py_table select'覅','fiao'
insert into py_table select'仏','fo'
insert into py_table select'垺','fou'
insert into py_table select'夫','fu'
insert into py_table select'猤','fui'
insert into py_table select'旮','ga'
insert into py_table select'侅','gai'
insert into py_table select'甘','gan'
insert into py_table select'冈','gang'
insert into py_table select'皋','gao'
insert into py_table select'戈','ge'
insert into py_table select'给','gei'
insert into py_table select'根','gen'
insert into py_table select'更','geng'
insert into py_table select'啹','geu'
insert into py_table select'喼','gib'
insert into py_table select'嗰','go'
insert into py_table select'工','gong'
insert into py_table select'兝','gongfen'
insert into py_table select'兣','gongli'
insert into py_table select'勾','gou'
insert into py_table select'估','gu'
insert into py_table select'瓜','gua'
insert into py_table select'乖','guai'
insert into py_table select'关','guan'
insert into py_table select'光','guang'
insert into py_table select'归','gui'
insert into py_table select'丨','gun'
insert into py_table select'呙','guo'
insert into py_table select'妎','ha'
insert into py_table select'咍','hai'
insert into py_table select'乤','hal'
insert into py_table select'兯','han'
insert into py_table select'魧','hang'
insert into py_table select'茠','hao'
insert into py_table select'兞','haoke'
insert into py_table select'诃','he'
insert into py_table select'黒','hei'
insert into py_table select'拫','hen'
insert into py_table select'亨','heng'
insert into py_table select'囍','heui'
insert into py_table select'乊','ho'
insert into py_table select'乥','hol'
insert into py_table select'叿','hong'
insert into py_table select'齁','hou'
insert into py_table select'乎','hu'
insert into py_table select'花','hua'
insert into py_table select'徊','huai'
insert into py_table select'欢','huan'
insert into py_table select'巟','huang'
insert into py_table select'灰','hui'
insert into py_table select'昏','hun'
insert into py_table select'吙','huo'
insert into py_table select'嚿','geo'
insert into py_table select'夻','hwa'
insert into py_table select'丌','ji'
insert into py_table select'加','jia'
insert into py_table select'嗧','jialun'
insert into py_table select'戋','jian'
insert into py_table select'江','jiang'
insert into py_table select'艽','jiao'
insert into py_table select'阶','jie'
insert into py_table select'巾','jin'
insert into py_table select'坕','jing'
insert into py_table select'冂','jiong'
insert into py_table select'丩','jiu'
insert into py_table select'欍','jou'
insert into py_table select'凥','ju'
insert into py_table select'姢','juan'
insert into py_table select'噘','jue'
insert into py_table select'军','jun'
insert into py_table select'咔','ka'
insert into py_table select'开','kai'
insert into py_table select'乫','kal'
insert into py_table select'刊','kan'
insert into py_table select'冚','hem'
insert into py_table select'砊','kang'
insert into py_table select'尻','kao'
insert into py_table select'坷','ke'
insert into py_table select'肎','ken'
insert into py_table select'劥','keng'
insert into py_table select'巪','keo'
insert into py_table select'乬','keol'
insert into py_table select'唟','keos'
insert into py_table select'厼','keum'
insert into py_table select'怾','ki'
insert into py_table select'空','kong'
insert into py_table select'廤','kos'
insert into py_table select'抠','kou'
insert into py_table select'扝','ku'
insert into py_table select'夸','kua'
insert into py_table select'蒯','kuai'
insert into py_table select'宽','kuan'
insert into py_table select'匡','kuang'
insert into py_table select'亏','kui'
insert into py_table select'坤','kun'
insert into py_table select'拡','kuo'
insert into py_table select'穒','kweok'
insert into py_table select'垃','la'
insert into py_table select'来','lai'
insert into py_table select'兰','lan'
insert into py_table select'啷','lang'
insert into py_table select'捞','lao'
insert into py_table select'仂','le'
insert into py_table select'雷','lei'
insert into py_table select'塄','leng'
insert into py_table select'唎','li'
insert into py_table select'俩','lia'
insert into py_table select'嫾','lian'
insert into py_table select'簗','liang'
insert into py_table select'蹽','liao'
insert into py_table select'毟','lie'
insert into py_table select'厸','lin'
insert into py_table select'伶','ling'
insert into py_table select'溜','liu'
insert into py_table select'瓼','liwa'
insert into py_table select'囖','lo'
insert into py_table select'龙','long'
insert into py_table select'娄','lou'
insert into py_table select'噜','lu'
insert into py_table select'驴','lv'
insert into py_table select'寽','lue'
insert into py_table select'孪','luan'
insert into py_table select'掄','lun'
insert into py_table select'頱','luo'
insert into py_table select'呣','m'
insert into py_table select'妈','ma'
insert into py_table select'遤','hweong'
insert into py_table select'埋','mai'
insert into py_table select'颟','man'
insert into py_table select'牤','mang'
insert into py_table select'匁','mangmi'
insert into py_table select'猫','mao'
insert into py_table select'唜','mas'
insert into py_table select'庅','me'
insert into py_table select'呅','mei'
insert into py_table select'椚','men'
insert into py_table select'掹','meng'
insert into py_table select'踎','meo'
insert into py_table select'瞇','mi'
insert into py_table select'宀','mian'
insert into py_table select'喵','miao'
insert into py_table select'乜','mie'
insert into py_table select'瓱','miliklanm'
insert into py_table select'民','min'
insert into py_table select'冧','lem'
insert into py_table select'名','ming'
insert into py_table select'谬','miu'
insert into py_table select'摸','mo'
insert into py_table select'乮','mol'
insert into py_table select'哞','mou'
insert into py_table select'母','mu'
insert into py_table select'旀','myeo'
insert into py_table select'丆','myeon'
insert into py_table select'椧','myeong'
insert into py_table select'拏','na'
insert into py_table select'腉','nai'
insert into py_table select'囡','nan'
insert into py_table select'囔','nang'
insert into py_table select'乪','keg'
insert into py_table select'孬','nao'
insert into py_table select'疒','ne'
insert into py_table select'娞','nei'
insert into py_table select'焾','nem'
insert into py_table select'嫩','nen'
insert into py_table select'莻','neus'
insert into py_table select'鈪','ngag'
insert into py_table select'銰','ngai'
insert into py_table select'啱','ngam'
insert into py_table select'妮','ni'
insert into py_table select'年','nian'
insert into py_table select'娘','niang'
insert into py_table select'茑','niao'
insert into py_table select'捏','nie'
insert into py_table select'脌','nin'
insert into py_table select'宁','ning'
insert into py_table select'牛','niu'
insert into py_table select'农','nong'
insert into py_table select'羺','nou'
insert into py_table select'奴','nu'
insert into py_table select'女','nv'
insert into py_table select'疟','nue'
insert into py_table select'瘧','nve'
insert into py_table select'奻','nuan'
insert into py_table select'黁','nun'
insert into py_table select'燶','nung'
insert into py_table select'挪','nuo'
insert into py_table select'筽','o'
insert into py_table select'夞','oes'
insert into py_table select'乯','ol'
insert into py_table select'鞰','on'
insert into py_table select'讴','ou'
insert into py_table select'妑','pa'
insert into py_table select'俳','pai'
insert into py_table select'磗','pak'
insert into py_table select'眅','pan'
insert into py_table select'乓','pang'
insert into py_table select'抛','pao'
insert into py_table select'呸','pei'
insert into py_table select'瓫','pen'
insert into py_table select'匉','peng'
insert into py_table select'浌','peol'
insert into py_table select'巼','phas'
insert into py_table select'闏','phdeng'
insert into py_table select'乶','phoi'
insert into py_table select'喸','phos'
insert into py_table select'丕','pi'
insert into py_table select'囨','pian'
insert into py_table select'缥','piao'
insert into py_table select'氕','pie'
insert into py_table select'丿','pianpang'
insert into py_table select'姘','pin'
insert into py_table select'乒','ping'
insert into py_table select'钋','po'
insert into py_table select'剖','pou'
insert into py_table select'哣','deo'
insert into py_table select'兺','ppun'
insert into py_table select'仆','pu'
insert into py_table select'七','qi'
insert into py_table select'掐','qia'
insert into py_table select'千','qian'
insert into py_table select'羌','qiang'
insert into py_table select'兛','qianke'
insert into py_table select'瓩','qianwa'
insert into py_table select'悄','qiao'
insert into py_table select'苆','qie'
insert into py_table select'亲','qin'
insert into py_table select'蠄','kem'
insert into py_table select'氢','qing'
insert into py_table select'銎','qiong'
insert into py_table select'丘','qiu'
insert into py_table select'曲','qu'
insert into py_table select'迲','keop'
insert into py_table select'峑','quan'
insert into py_table select'蒛','que'
insert into py_table select'夋','qun'
insert into py_table select'亽','ra'
insert into py_table select'囕','ram'
insert into py_table select'呥','ran'
insert into py_table select'穣','rang'
insert into py_table select'荛','rao'
insert into py_table select'惹','re'
insert into py_table select'人','ren'
insert into py_table select'扔','reng'
insert into py_table select'日','ri'
insert into py_table select'栄','rong'
insert into py_table select'禸','rou'
insert into py_table select'嶿','ru'
insert into py_table select'撋','ruan'
insert into py_table select'桵','rui'
insert into py_table select'闰','run'
insert into py_table select'叒','ruo'
insert into py_table select'仨','sa'
insert into py_table select'栍','saeng'
insert into py_table select'毢','sai'
insert into py_table select'虄','sal'
insert into py_table select'三','san'
insert into py_table select'桒','sang'
insert into py_table select'掻','sao'
insert into py_table select'色','se'
insert into py_table select'裇','sed'
insert into py_table select'聓','sei'
insert into py_table select'森','sen'
insert into py_table select'鬙','seng'
insert into py_table select'閪','seo'
insert into py_table select'縇','seon'
insert into py_table select'杀','sha'
insert into py_table select'筛','shai'
insert into py_table select'山','shan'
insert into py_table select'伤','shang'
insert into py_table select'弰','shao'
insert into py_table select'奢','she'
insert into py_table select'申','shen'
insert into py_table select'升','sheng'
insert into py_table select'尸','shi'
insert into py_table select'兙','shike'
insert into py_table select'瓧','shiwa'
insert into py_table select'収','shou'
insert into py_table select'书','shu'
insert into py_table select'刷','shua'
insert into py_table select'摔','shuai'
insert into py_table select'闩','shuan'
insert into py_table select'双','shuang'
insert into py_table select'谁','shei'
insert into py_table select'脽','shui'
insert into py_table select'吮','shun'
insert into py_table select'哾','shuo'
insert into py_table select'丝','si'
insert into py_table select'螦','so'
insert into py_table select'乺','sol'
insert into py_table select'忪','song'
insert into py_table select'凁','sou'
insert into py_table select'苏','su'
insert into py_table select'痠','suan'
insert into py_table select'夊','sui'
insert into py_table select'孙','sun'
insert into py_table select'娑','suo'
insert into py_table select'他','ta'
insert into py_table select'襨','tae'
insert into py_table select'囼','tai'
insert into py_table select'坍','tan'
insert into py_table select'铴','tang'
insert into py_table select'仐','tao'
insert into py_table select'畓','tap'
insert into py_table select'忒','te'
insert into py_table select'膯','teng'
insert into py_table select'唞','teo'
insert into py_table select'朰','teul'
insert into py_table select'剔','ti'
insert into py_table select'天','tian'
insert into py_table select'旫','tiao'
insert into py_table select'怗','tie'
insert into py_table select'厅','ting'
insert into py_table select'乭','tol'
insert into py_table select'囲','tong'
insert into py_table select'偷','tou'
insert into py_table select'凸','tu'
insert into py_table select'湍','tuan'
insert into py_table select'推','tui'
insert into py_table select'旽','tun'
insert into py_table select'乇','tuo'
insert into py_table select'屲','wa'
insert into py_table select'歪','wai'
insert into py_table select'乛','wan'
insert into py_table select'尣','wang'
insert into py_table select'危','wei'
insert into py_table select'塭','wen'
insert into py_table select'翁','weng'
insert into py_table select'挝','wo'
insert into py_table select'乌','wu'
insert into py_table select'夕','xi'
insert into py_table select'诶','ei'
insert into py_table select'疨','xia'
insert into py_table select'仙','xian'
insert into py_table select'乡','xiang'
insert into py_table select'灱','xiao'
insert into py_table select'楔','xie'
insert into py_table select'心','xin'
insert into py_table select'星','xing'
insert into py_table select'凶','xiong'
insert into py_table select'休','xiu'
insert into py_table select'旴','xu'
insert into py_table select'昍','xuan'
insert into py_table select'疶','xue'
insert into py_table select'坃','xun'
insert into py_table select'丫','ya'
insert into py_table select'咽','yan'
insert into py_table select'欕','eom'
insert into py_table select'央','yang'
insert into py_table select'吆','yao'
insert into py_table select'椰','ye'
insert into py_table select'膶','yen'
insert into py_table select'一','yi'
insert into py_table select'乁','i'
insert into py_table select'乚','yin'
insert into py_table select'应','ying'
insert into py_table select'哟','yo'
insert into py_table select'佣','yong'
insert into py_table select'优','you'
insert into py_table select'迂','yu'
insert into py_table select'囦','yuan'
insert into py_table select'曰','yue'
insert into py_table select'蒀','yun'
insert into py_table select'帀','za'
insert into py_table select'災','zai'
insert into py_table select'兂','zan'
insert into py_table select'牂','zang'
insert into py_table select'遭','zao'
insert into py_table select'啫','ze'
insert into py_table select'贼','zei'
insert into py_table select'怎','zen'
insert into py_table select'曽','zeng'
insert into py_table select'吒','zha'
insert into py_table select'甴','gad'
insert into py_table select'夈','zhai'
insert into py_table select'毡','zhan'
insert into py_table select'张','zhang'
insert into py_table select'钊','zhao'
insert into py_table select'蜇','zhe'
insert into py_table select'贞','zhen'
insert into py_table select'凧','zheng'
insert into py_table select'之','zhi'
insert into py_table select'中','zhong'
insert into py_table select'州','zhou'
insert into py_table select'劯','zhu'
insert into py_table select'抓','zhua'
insert into py_table select'专','zhuan'
insert into py_table select'转','zhuai'
insert into py_table select'妆','zhuang'
insert into py_table select'骓','zhui'
insert into py_table select'宒','zhun'
insert into py_table select'卓','zhuo'
insert into py_table select'孜','zi'
insert into py_table select'唨','zo'
insert into py_table select'宗','zong'
insert into py_table select'棸','zou'
insert into py_table select'哫','zu'
insert into py_table select'劗','zuan'
insert into py_table select'厜','zui'
insert into py_table select'尊','zun'
insert into py_table select'昨','zuo' alter function f_Getpinyin(@str varchar(100))
returns varchar(8000)
as
begin
declare @re varchar(8000) declare @strlen int
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=UPPER(py)+@re,@strlen=@strlen-1
from py_table a where chr<=substring(@str,@strlen,1)
order by chr collate Chinese_PRC_CS_AS_KS_WS desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
set @re = UPPER(@re)
return(@re)
end
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
declare @t nvarchar(10)
set @t = '张三啊'
select dbo.fun_getPY(substring(@t,2,10)) + dbo.f_Getpinyin(substring(@t,1,1))
----------------
--SAZHANG
用这两个函数的确解决了。小弟学习了！
Thanks!