sql数据库
自定义一个函数
把下面代码写进去
功能是得到汉字拼音首字母
如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
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('中华人民共和国')
结果都为:ZHRMGHG
/* -------------------------------------------------------------
函数: fn_GetPinyin
描述: 汉字转拼音(无数据表版)
使用: dbo.fn_GetPinyin('中华人民共和国') = zhonghuarenmingongheguo
作者: 流香羽 (改编:Tony)
博客: http://hi.baidu.com/流香羽
------------------------------------------------------------- */
IF OBJECT_ID(
'[fn_GetPinyin]'
)
IS
NOT
NULL
DROP
FUNCTION
[fn_GetPinyin]
GO
create
function
[dbo].[fn_GetPinyin](@words nvarchar(2000))
returns
varchar
(8000)
as
begin
declare
@word
nchar
(1)
declare
@pinyin
varchar
(8000)
declare
@i
int
declare
@words_len
int
declare
@unicode
int
set
@i = 1
set
@words = ltrim(rtrim(@words))
set
@words_len = len(@words)
while (@i <= @words_len)
--循环取字符
begin
set
@word =
substring
(@words, @i, 1)
set
@unicode = unicode(@word)
set
@pinyin =
ISNULL
(@pinyin +
SPACE
(1),
''
)+
(
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
'ai'
,N
'靉'
union
all
select
'an'
,N
'黯'
union
all
select
'ang'
,N
'醠'
union
all
select
'ao'
,N
'驁'
union
all
select
'ba'
,N
'欛'
union
all
select
'bai'
,N
'瓸'
--韛兡瓸
union
all
select
'ban'
,N
'瓣'
union
all
select
'bang'
,N
'鎊'
union
all
select
'bao'
,N
'鑤'
union
all
select
'bei'
,N
'鐾'
union
all
select
'ben'
,N
'輽'
union
all
select
'beng'
,N
'鏰'
union
all
select
'bi'
,N
'鼊'
union
all
select
'bian'
,N
'變'
union
all
select
'biao'
,N
'鰾'
union
all
select
'bie'
,N
'彆'
union
all
select
'bin'
,N
'鬢'
union
all
select
'bing'
,N
'靐'
union
all
select
'bo'
,N
'蔔'
union
all
select
'bu'
,N
'簿'
union
all
select
'ca'
,N
'囃'
union
all
select
'cai'
,N
'乲'
--縩乲
union
all
select
'can'
,N
'爘'
union
all
select
'cang'
,N
'賶'
union
all
select
'cao'
,N
'鼜'
union
all
select
'ce'
,N
'簎'
union
all
select
'cen'
,N
'笒'
union
all
select
'ceng'
,N
'乽'
--硛硳岾猠乽
union
all
select
'cha'
,N
'詫'
union
all
select
'chai'
,N
'囆'
union
all
select
'chan'
,N
'顫'
union
all
select
'chang'
,N
'韔'
union
all
select
'chao'
,N
'觘'
union
all
select
'che'
,N
'爡'
union
all
select
'chen'
,N
'讖'
union
all
select
'cheng'
,N
'秤'
union
all
select
'chi'
,N
'鷘'
union
all
select
'chong'
,N
'銃'
union
all
select
'chou'
,N
'殠'
union
all
select
'chu'
,N
'矗'
union
all
select
'chuai'
,N
'踹'
union
all
select
'chuan'
,N
'鶨'
union
all
select
'chuang'
,N
'愴'
union
all
select
'chui'
,N
'顀'
union
all
select
'chun'
,N
'蠢'
union
all
select
'chuo'
,N
'縒'
union
all
select
'ci'
,N
'嗭'
--賜嗭
union
all
select
'cong'
,N
'謥'
union
all
select
'cou'
,N
'輳'
union
all
select
'cu'
,N
'顣'
union
all
select
'cuan'
,N
'爨'
union
all
select
'cui'
,N
'臎'
union
all
select
'cun'
,N
'籿'
union
all
select
'cuo'
,N
'錯'
union
all
select
'da'
,N
'橽'
union
all
select
'dai'
,N
'靆'
union
all
select
'dan'
,N
'饏'
union
all
select
'dang'
,N
'闣'
union
all
select
'dao'
,N
'纛'
union
all
select
'de'
,N
'的'
union
all
select
'den'
,N
'扽'
union
all
select
'deng'
,N
'鐙'
union
all
select
'di'
,N
'螮'
union
all
select
'dia'
,N
'嗲'
union
all
select
'dian'
,N
'驔'
union
all
select
'diao'
,N
'鑃'
union
all
select
'die'
,N
'嚸'
--眰嚸
union
all
select
'ding'
,N
'顁'
union
all
select
'diu'
,N
'銩'
union
all
select
'dong'
,N
'霘'
union
all
select
'dou'
,N
'鬭'
union
all
select
'du'
,N
'蠹'
union
all
select
'duan'
,N
'叾'
--籪叾
union
all
select
'dui'
,N
'譵'
union
all
select
'dun'
,N
'踲'
union
all
select
'duo'
,N
'鵽'
union
all
select
'e'
,N
'鱷'
union
all
select
'en'
,N
'摁'
union
all
select
'eng'
,N
'鞥'
union
all
select
'er'
,N
'樲'
union
all
select
'fa'
,N
'髮'
union
all
select
'fan'
,N
'瀪'
union
all
select
'fang'
,N
'放'
union
all
select
'fei'
,N
'靅'
union
all
select
'fen'
,N
'鱝'
union
all
select
'feng'
,N
'覅'
union
all
select
'fo'
,N
'梻'
union
all
select
'fou'
,N
'鴀'
union
all
select
'fu'
,N
'猤'
--鰒猤
union
all
select
'ga'
,N
'魀'
union
all
select
'gai'
,N
'瓂'
union
all
select
'gan'
,N
'灨'
union
all
select
'gang'
,N
'戇'
union
all
select
'gao'
,N
'鋯'
union
all
select
'ge'
,N
'獦'
union
all
select
'gei'
,N
'給'
union
all
select
'gen'
,N
'搄'
union
all
select
'geng'
,N
'堩'
--亙堩啹喼嗰
union
all
select
'gong'
,N
'兣'
--熕贑兝兣
union
all
select
'gou'
,N
'購'
union
all
select
'gu'
,N
'顧'
union
all
select
'gua'
,N
'詿'
union
all
select
'guai'
,N
'恠'
union
all
select
'guan'
,N
'鱹'
union
all
select
'guang'
,N
'撗'
union
all
select
'gui'
,N
'鱥'
union
all
select
'gun'
,N
'謴'
union
all
select
'guo'
,N
'腂'
union
all
select
'ha'
,N
'哈'
union
all
select
'hai'
,N
'饚'
union
all
select
'han'
,N
'鶾'
union
all
select
'hang'
,N
'沆'
union
all
select
'hao'
,N
'兞'
union
all
select
'he'
,N
'靏'
union
all
select
'hei'
,N
'嬒'
union
all
select
'hen'
,N
'恨'
union
all
select
'heng'
,N
'堼'
--堼囍
union
all
select
'hong'
,N
'鬨'
union
all
select
'hou'
,N
'鱟'
union
all
select
'hu'
,N
'鸌'
union
all
select
'hua'
,N
'蘳'
union
all
select
'huai'
,N
'蘾'
union
all
select
'huan'
,N
'鰀'
union
all
select
'huang'
,N
'鎤'
union
all
select
'hui'
,N
'顪'
union
all
select
'hun'
,N
'諢'
union
all
select
'huo'
,N
'夻'
union
all
select
'ji'
,N
'驥'
union
all
select
'jia'
,N
'嗧'
union
all
select
'jian'
,N
'鑳'
union
all
select
'jiang'
,N
'謽'
union
all
select
'jiao'
,N
'釂'
union
all
select
'jie'
,N
'繲'
union
all
select
'jin'
,N
'齽'
union
all
select
'jing'
,N
'竸'
union
all
select
'jiong'
,N
'蘔'
union
all
select
'jiu'
,N
'欍'
union
all
select
'ju'
,N
'爠'
union
all
select
'juan'
,N
'羂'
union
all
select
'jue'
,N
'钁'
union
all
select
'jun'
,N
'攈'
union
all
select
'ka'
,N
'鉲'
union
all
select
'kai'
,N
'乫'
--鎎乫
union
all
select
'kan'
,N
'矙'
union
all
select
'kang'
,N
'閌'
union
all
select
'kao'
,N
'鯌'
union
all
select
'ke'
,N
'騍'
union
all
select
'ken'
,N
'褃'
union
all
select
'keng'
,N
'鏗'
--巪乬唟厼怾
union
all
select
'kong'
,N
'廤'
union
all
select
'kou'
,N
'鷇'
union
all
select
'ku'
,N
'嚳'
union
all
select
'kua'
,N
'骻'
union
all
select
'kuai'
,N
'鱠'
union
all
select
'kuan'
,N
'窾'
union
all
select
'kuang'
,N
'鑛'
union
all
select
'kui'
,N
'鑎'
union
all
select
'kun'
,N
'睏'
union
all
select
'kuo'
,N
'穒'
union
all
select
'la'
,N
'鞡'
union
all
select
'lai'
,N
'籟'
union
all
select
'lan'
,N
'糷'
union
all
select
'lang'
,N
'唥'
union
all
select
'lao'
,N
'軂'
union
all
select
'le'
,N
'餎'
union
all
select
'lei'
,N
'脷'
--嘞脷
union
all
select
'leng'
,N
'睖'
union
all
select
'li'
,N
'瓈'
union
all
select
'lia'
,N
'倆'
union
all
select
'lian'
,N
'纞'
union
all
select
'liang'
,N
'鍄'
union
all
select
'liao'
,N
'瞭'
union
all
select
'lie'
,N
'鱲'
union
all
select
'lin'
,N
'轥'
--轥拎
union
all
select
'ling'
,N
'炩'
union
all
select
'liu'
,N
'咯'
--瓼甅囖咯
union
all
select
'long'
,N
'贚'
union
all
select
'lou'
,N
'鏤'
union
all
select
'lu'
,N
'氇'
union
all
select
'lv'
,N
'鑢'
union
all
select
'luan'
,N
'亂'
union
all
select
'lue'
,N
'擽'
union
all
select
'lun'
,N
'論'
union
all
select
'luo'
,N
'鱳'
union
all
select
'ma'
,N
'嘛'
union
all
select
'mai'
,N
'霢'
union
all
select
'man'
,N
'蘰'
union
all
select
'mang'
,N
'蠎'
union
all
select
'mao'
,N
'唜'
union
all
select
'me'
,N
'癦'
--癦呅
union
all
select
'mei'
,N
'嚜'
union
all
select
'men'
,N
'們'
union
all
select
'meng'
,N
'霥'
--霿踎
union
all
select
'mi'
,N
'羃'
union
all
select
'mian'
,N
'麵'
union
all
select
'miao'
,N
'廟'
union
all
select
'mie'
,N
'鱴'
--鱴瓱
union
all
select
'min'
,N
'鰵'
union
all
select
'ming'
,N
'詺'
union
all
select
'miu'
,N
'謬'
union
all
select
'mo'
,N
'耱'
--耱乮
union
all
select
'mou'
,N
'麰'
--麰蟱
union
all
select
'mu'
,N
'旀'
union
all
select
'na'
,N
'魶'
union
all
select
'nai'
,N
'錼'
union
all
select
'nan'
,N
'婻'
union
all
select
'nang'
,N
'齉'
union
all
select
'nao'
,N
'臑'
union
all
select
'ne'
,N
'呢'
union
all
select
'nei'
,N
'焾'
--嫩焾
union
all
select
'nen'
,N
'嫩'
union
all
select
'neng'
,N
'能'
--莻嗯鈪銰啱
union
all
select
'ni'
,N
'嬺'
union
all
select
'nian'
,N
'艌'
union
all
select
'niang'
,N
'釀'
union
all
select
'niao'
,N
'脲'
union
all
select
'nie'
,N
'钀'
union
all
select
'nin'
,N
'拰'
union
all
select
'ning'
,N
'濘'
union
all
select
'niu'
,N
'靵'
union
all
select
'nong'
,N
'齈'
union
all
select
'nou'
,N
'譳'
union
all
select
'nu'
,N
'搙'
union
all
select
'nv'
,N
'衄'
union
all
select
'nue'
,N
'瘧'
union
all
select
'nuan'
,N
'燶'
--硸黁燶郍
union
all
select
'nuo'
,N
'桛'
union
all
select
'o'
,N
'鞰'
--毮夞乯鞰
union
all
select
'ou'
,N
'漚'
union
all
select
'pa'
,N
'袙'
union
all
select
'pai'
,N
'磗'
--鎃磗
union
all
select
'pan'
,N
'鑻'
union
all
select
'pang'
,N
'胖'
union
all
select
'pao'
,N
'礮'
union
all
select
'pei'
,N
'轡'
union
all
select
'pen'
,N
'喯'
union
all
select
'peng'
,N
'喸'
--浌巼闏乶喸
union
all
select
'pi'
,N
'鸊'
union
all
select
'pian'
,N
'騙'
union
all
select
'piao'
,N
'慓'
union
all
select
'pie'
,N
'嫳'
union
all
select
'pin'
,N
'聘'
union
all
select
'ping'
,N
'蘋'
union
all
select
'po'
,N
'魄'
union
all
select
'pou'
,N
'哛'
--兺哛
union
all
select
'pu'
,N
'曝'
union
all
select
'qi'
,N
'蟿'
union
all
select
'qia'
,N
'髂'
union
all
select
'qian'
,N
'縴'
union
all
select
'qiang'
,N
'瓩'
--羻兛瓩
union
all
select
'qiao'
,N
'躈'
union
all
select
'qie'
,N
'籡'
union
all
select
'qin'
,N
'藽'
union
all
select
'qing'
,N
'櫦'
union
all
select
'qiong'
,N
'瓗'
union
all
select
'qiu'
,N
'糗'
union
all
select
'qu'
,N
'覻'
union
all
select
'quan'
,N
'勸'
union
all
select
'que'
,N
'礭'
union
all
select
'qun'
,N
'囕'
union
all
select
'ran'
,N
'橪'
union
all
select
'rang'
,N
'讓'
union
all
select
'rao'
,N
'繞'
union
all
select
're'
,N
'熱'
union
all
select
'ren'
,N
'餁'
union
all
select
'reng'
,N
'陾'
union
all
select
'ri'
,N
'馹'
union
all
select
'rong'
,N
'穃'
union
all
select
'rou'
,N
'嶿'
union
all
select
'ru'
,N
'擩'
union
all
select
'ruan'
,N
'礝'
union
all
select
'rui'
,N
'壡'
union
all
select
'run'
,N
'橍'
--橍挼
union
all
select
'ruo'
,N
'鶸'
union
all
select
'sa'
,N
'栍'
--櫒栍
union
all
select
'sai'
,N
'虄'
--簺虄
union
all
select
'san'
,N
'閐'
union
all
select
'sang'
,N
'喪'
union
all
select
'sao'
,N
'髞'
union
all
select
'se'
,N
'飋'
--裇聓
union
all
select
'sen'
,N
'篸'
union
all
select
'seng'
,N
'縇'
--閪縇
union
all
select
'sha'
,N
'霎'
union
all
select
'shai'
,N
'曬'
union
all
select
'shan'
,N
'鱔'
union
all
select
'shang'
,N
'緔'
union
all
select
'shao'
,N
'潲'
union
all
select
'she'
,N
'欇'
union
all
select
'shen'
,N
'瘮'
union
all
select
'sheng'
,N
'賸'
union
all
select
'shi'
,N
'瓧'
--鰘齛兙瓧
union
all
select
'shou'
,N
'鏉'
union
all
select
'shu'
,N
'虪'
union
all
select
'shua'
,N
'誜'
union
all
select
'shuai'
,N
'卛'
union
all
select
'shuan'
,N
'腨'
union
all
select
'shuang'
,N
'灀'
union
all
select
'shui'
,N
'睡'
union
all
select
'shun'
,N
'鬊'
union
all
select
'shuo'
,N
'鑠'
union
all
select
'si'
,N
'乺'
--瀃螦乺
union
all
select
'song'
,N
'鎹'
union
all
select
'sou'
,N
'瘶'
union
all
select
'su'
,N
'鷫'
union
all
select
'suan'
,N
'算'
union
all
select
'sui'
,N
'鐩'
union
all
select
'sun'
,N
'潠'
union
all
select
'suo'
,N
'蜶'
union
all
select
'ta'
,N
'襨'
--躢襨
union
all
select
'tai'
,N
'燤'
union
all
select
'tan'
,N
'賧'
union
all
select
'tang'
,N
'燙'
union
all
select
'tao'
,N
'畓'
--討畓
union
all
select
'te'
,N
'蟘'
union
all
select
'teng'
,N
'朰'
--霯唞朰
union
all
select
'ti'
,N
'趯'
union
all
select
'tian'
,N
'舚'
union
all
select
'tiao'
,N
'糶'
union
all
select
'tie'
,N
'餮'
union
all
select
'ting'
,N
'乭'
--濎乭
union
all
select
'tong'
,N
'憅'
union
all
select
'tou'
,N
'透'
union
all
select
'tu'
,N
'鵵'
union
all
select
'tuan'
,N
'褖'
union
all
select
'tui'
,N
'駾'
union
all
select
'tun'
,N
'坉'
union
all
select
'tuo'
,N
'籜'
union
all
select
'wa'
,N
'韤'
union
all
select
'wai'
,N
'顡'
union
all
select
'wan'
,N
'贎'
union
all
select
'wang'
,N
'朢'
union
all
select
'wei'
,N
'躛'
union
all
select
'wen'
,N
'璺'
union
all
select
'weng'
,N
'齆'
union
all
select
'wo'
,N
'齷'
union
all
select
'wu'
,N
'鶩'
union
all
select
'xi'
,N
'衋'
union
all
select
'xia'
,N
'鏬'
union
all
select
'xian'
,N
'鼸'
union
all
select
'xiang'
,N
'鱌'
union
all
select
'xiao'
,N
'斆'
union
all
select
'xie'
,N
'躞'
union
all
select
'xin'
,N
'釁'
union
all
select
'xing'
,N
'臖'
union
all
select
'xiong'
,N
'敻'
union
all
select
'xiu'
,N
'齅'
union
all
select
'xu'
,N
'蓿'
union
all
select
'xuan'
,N
'贙'
union
all
select
'xue'
,N
'瀥'
union
all
select
'xun'
,N
'鑂'
union
all
select
'ya'
,N
'齾'
union
all
select
'yan'
,N
'灩'
union
all
select
'yang'
,N
'樣'
union
all
select
'yao'
,N
'鑰'
union
all
select
'ye'
,N
'岃'
--鸈膶岃
union
all
select
'yi'
,N
'齸'
union
all
select
'yin'
,N
'檼'
union
all
select
'ying'
,N
'譍'
union
all
select
'yo'
,N
'喲'
union
all
select
'yong'
,N
'醟'
union
all
select
'you'
,N
'鼬'
union
all
select
'yu'
,N
'爩'
union
all
select
'yuan'
,N
'願'
union
all
select
'yue'
,N
'鸙'
union
all
select
'yun'
,N
'韻'
union
all
select
'za'
,N
'雥'
union
all
select
'zai'
,N
'縡'
union
all
select
'zan'
,N
'饡'
union
all
select
'zang'
,N
'臟'
union
all
select
'zao'
,N
'竈'
union
all
select
'ze'
,N
'稄'
union
all
select
'zei'
,N
'鱡'
union
all
select
'zen'
,N
'囎'
union
all
select
'zeng'
,N
'贈'
union
all
select
'zha'
,N
'醡'
union
all
select
'zhai'
,N
'瘵'
union
all
select
'zhan'
,N
'驏'
union
all
select
'zhang'
,N
'瞕'
union
all
select
'zhao'
,N
'羄'
union
all
select
'zhe'
,N
'鷓'
union
all
select
'zhen'
,N
'黮'
union
all
select
'zheng'
,N
'證'
union
all
select
'zhi'
,N
'豒'
union
all
select
'zhong'
,N
'諥'
union
all
select
'zhou'
,N
'驟'
union
all
select
'zhu'
,N
'鑄'
union
all
select
'zhua'
,N
'爪'
union
all
select
'zhuai'
,N
'跩'
union
all
select
'zhuan'
,N
'籑'
union
all
select
'zhuang'
,N
'戅'
union
all
select
'zhui'
,N
'鑆'
union
all
select
'zhun'
,N
'稕'
union
all
select
'zhuo'
,N
'籱'
union
all
select
'zi'
,N
'漬'
--漬唨
union
all
select
'zong'
,N
'縱'
union
all
select
'zou'
,N
'媰'
union
all
select
'zu'
,N
'謯'
union
all
select
'zuan'
,N
'攥'
union
all
select
'zui'
,N
'欈'
union
all
select
'zun'
,N
'銌'
union
all
select
'zuo'
,N
'咗'
) t
where
word >= @word
collate
Chinese_PRC_CS_AS_KS_WS
order
by
word
collate
Chinese_PRC_CS_AS_KS_WS
ASC
)
else
@word
end
)
set
@i = @i + 1
end
return
@pinyin
END
GO
|