MSSQL Chinese character to pinyin function implementation statement

Source: Internet
Author: User
Experience in converting Chinese characters to pinyin (non-data table version) from the acquisition to the Pinyin function

Experience in converting Chinese characters to pinyin (non-data table version) from the acquisition to the Pinyin function

The code is as follows:
/*-------------------------------------------------------------
Function: fn_GetPinyin
Description: conversion of Chinese characters to pinyin (non-data table version)
Use: dbo. fn_GetPinyin ('People's Republic of China') = zhonghuarenmingongheguo
Author: Liu Xiangyu (adapted: Tony)
-------------------------------------------------------------*/

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) -- cyclically retrieves characters
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' comment 'as word
Union all select 'Ai', N 'hangzhou'
Union all select 'any', n' then'
Union all select 'ang ', N 'hangzhou'
Union all select 'ao', N 'hangzhou'
Union all select 'ba', N 'hangzhou'
Union all select 'Bai ', N 'hangzhou' -- has been deleted
Union all select 'ban ', N 'AP'
Union all select 'bang', N 'hangzhou'
Union all select 'bao', n' then'
Union all select 'bei', N 'Shanghai'
Union all select 'Ben', N 'hangzhou'
Union all select 'beng', n' then'
Union all select 'bi', 'n' comment'
Union all select 'bian ', n' change'
Union all select 'biao', N 'region'
Union all select 'bie ', n' else'
Union all select 'bin', N 'hangzhou'
Union all select 'Bing ', n' else'
Union all select 'Bo', N 'hangzhou'
Union all select 'bu ', n''
Union all select 'CA', N 'hangzhou'
Union all select 'Cai ', N 'hangzhou' -- Aggregate
Union all select 'Can ', N 'region'
Union all select 'cang ', N 'hangzhou'
Union all select 'Cao ', N 'hangzhou'
Union all select 'Ce ', n' else'
Union all select 'cen', N 'hangzhou'
Union all select 'ceng', N 'hangzhou' -- the specified parameter has been deleted
Union all select 'Cha ', N 'hangzhou'
Union all select 'Chai', N 'Shanghai'
Union all select 'chan ', N 'hangzhou'
Union all select 'Chang', N 'hangzhou'
Union all select 'chuno', N 'hangzhou'
Union all select 'ch', N 'hangzhou'
Union all select 'Chen ', N 'Chen'
Union all select 'hangzhou', N 'weight'
Union all select 'Chi ', N 'hangzhou'
Union all select 'Chong ', N 'hangzhou'
Union all select 'chou', N 'hangzhou'
Union all select 'chu', N 'hangzhou'
Union all select 'chuai', N 'ta'
Union all select 'Shanghai', N 'Shanghai'
Union all select 'Shanghai', N 'hangzhou'
Union all select 'chui ', N 'Shanghai'
Union all select 'chun', N 'dump'
Union all select 'chuo', n' then'
Union all select 'cies', n'region' -- distinct
Union all select 'cong', N 'hangzhou'
Union all select 'cou', N 'hangzhou'
Union all select 'CU ', N 'hour'
Union all select 'cuance', N 'hangzhou'
Union all select 'Cui', n' else'
Union all select 'cun', n' then'
Union all select 'cuo ', N 'hangzhou'
Union all select 'da', N 'region'
Union all select 'Dai', N 'hangzhou'
Union all select 'Dan', N 'hangzhou'
Union all select 'Dang ', N 'hangzhou'
Union all select 'Dao ', n' else'
Union all select 'de', n'
Union all select 'den ', N 'hour'
Union all select 'deng', N 'hangzhou'
Union all select 'di', N 'region'
Union all select 'Dia ', N 'region'
Union all select 'Dian ', N 'region'
Union all select 'diao', N 'region'
Union all select 'die', N 'region' -- distinct
Union all select 'ding', N 'hangzhou'
Union all select 'diu', N 'region'
Union all select 'dong', n' then'
Union all select 'dou', N 'region'
Union all select 'du', N 'region'
Union all select 'duany', n'taobao' -- distinct
Union all select 'DUI', n' comment'
Union all select 'dun', N 'region'
Union all select 'Duo', n' then'
Union all select 'e', n' then'
Union all select 'en', n''
Union all select 'eng ', N 'hangzhou'
Union all select 'ER', N 'region'
Union all select 'FA ', N 'hangzhou'
Union all select 'fan ', N 'hangzhou'
Union all select 'fang ', n' put'
Union all select 'fei', n' then'
Union all select 'fen', n' then'
Union all select 'Feng', n' then'
Union all select 'fo', N 'hangzhou'
Union all select 'fou', N 'hangzhou'
Union all select 'Fu', N 'hangzhou' -- Aggregate
Union all select 'ga ', N 'hangzhou'
Union all select 'gai', n' else'
Union all select 'Gan ', N 'hangzhou'
Union all select 'giang ', N 'hangzhou'
Union all select 'gao', n' then'
Union all select 'GE', N 'hour'
Union all select 'gei', n''
Union all select 'Gen ', N 'hour'
Union all select 'geng', N 'hangzhou' -- returns Returns returns
Union all select 'Gong ', N 'Shanghai' -- returns Returns returns
Union all select 'gou', N 'hangzhou'
Union all select 'Gu ', N 'hangzhou'
Union all select 'gua', n' then'
Union all select 'guai', N 'hangzhou'
Union all select 'guany', N 'hangzhou'
Union all select 'guang ', N 'hangzhou'
Union all select 'gu', N 'Shanghai'
Union all select 'gun', N 'hangzhou'
Union all select 'guo', n' then'
Union all select 'Ha ', N 'Ha'
Union all select 'hai ', N 'hangzhou'
Union all select 'Han', N 'hangzhou'
Union all select 'hang', N 'hangzhou'
Union all select 'hao', n' then'
Union all select 'He ', n' else'
Union all select 'hei', N 'Shanghai'
Union all select 'distinct', n' hate'
Union all select 'hangzhou', N 'hangzhou' -- Aggregate
Union all select 'Hong ', N 'hangzhou'
Union all select 'hou', N 'hangzhou'
Union all select 'hu', N 'hangzhou'
Union all select 'hua', N 'Shanghai'
Union all select 'huai', N 'Shanghai'
Union all select 'huany', n' then'
Union all select 'Huang ', N 'hangzhou'
Union all select 'hui', n' then'
Union all select 'Hun', n' then'
Union all select 'huo', n' then'
Union all select 'Ji ', N 'hangzhou'
Union all select 'Jia ', n' then'
Union all select 'jian ', n' else'
Union all select 'Jiang ', N 'hangzhou'
Union all select 'shao', N 'Shanghai'
Union all select 'jie', n' else'
Union all select 'Jin', n' then'
Union all select 'Jing', N 'hangzhou'
Union all select 'jiong ', N 'hangzhou'
Union all select 'jiu', n' else'
Union all select 'ju', N 'region'
Union all select 'juany', n' then'
Union all select 'jue ', n' else'
Union all select 'Jun', n' then'
Union all select 'Ka ', N 'hangzhou'
Union all select 'Kai', N 'hangzhou' -- Aggregate
Union all select 'kan ', N 'hangzhou'
Union all select 'hang', N 'hangzhou'
Union all select 'kao', N 'hangzhou'
Union all select 'ke', N 'hour'
Union all select 'Ken', N 'hangzhou'
Union all select 'keng', N 'hangzhou' -- returns two distinct
Union all select 'Kong ', N 'hangzhou'
Union all select 'kou', N 'hangzhou'
Union all select 'Ku ', N 'hangzhou'
Union all select 'kua', n' then'
Union all select 'kuai', n' then'
Union all select 'kuany', n' then'
Union all select 'kuang ', N 'hangzhou'
Union all select 'kui', N 'Shanghai'
Union all select 'kn', N 'hangzhou'
Union all select 'kuo', n' then'
Union all select 'La ', n' then'
Union all select 'Les', N 'hangzhou'
Union all select 'Lan ', N 'hangzhou'
Union all select 'Lang ', N 'hangzhou'
Union all select 'Lao', n' then'
Union all select 'le', n' then'
Union all select 'Lei', n' comment '-- distinct
Union all select 'leng', N 'hangzhou'
Union all select 'Lil', n' then'
Union all select 'lia ', N 'clerk'
Union all select 'liany', n' then'
Union all select 'Shanghai', N 'Shanghai'
Union all select 'liao', n'
Union all select 'Lil', N 'married'
Union all select 'lin', n'line' -- skip
Union all select 'ler', N 'hangzhou'
Union all select 'Liu ', n'' -- limit
Union all select 'long', N 'hangzhou'
Union all select 'Lou ', N 'hangzhou'
Union all select 'Lu', N 'region'
Union all select 'lv', N 'hangzhou'
Union all select 'luand', N 'hangzhou'
Union all select 'lue', N 'hangzhou'
Union all select 'lun ', N' comment'
Union all select 'luo', n' then'
Union all select 'M', n' well'
Union all select 'Mai', n' then'
Union all select 'Man ', n' else'
Union all select 'MANG', N 'hangzhou'
Union all select 'Mao ', N 'hangzhou'
Union all select 'Me ', N 'hour' -- returns comma
Union all select 'MEI', n' then'
Union all select 'Men ', nes'
Union all select 'meng', N 'hangzhou' -- Aggregate
Union all select 'MI', N 'clerk'
Union all select 'miany', n' then'
Union all select 'Miao', n' then'
Union all select 'mie ', N 'clerk' -- distinct
Union all select 'min', N 'hour'
Union all select 'Ming', N 'region'
Union all select 'miu', N 'delimiter'
Union all select 'M', N 'hour' -- returns comma
Union all select 'Mou', N 'hangzhou' -- Aggregate
Union all select 'Mu ', N 'hour'
Union all select 'Na', N 'hangzhou'
Union all select 'Nai', n' then'
Union all select 'Nan ', N 'hangzhou'
Union all select 'nang ', N 'hangzhou'
Union all select 'nao', n' then'
Union all select 'ne ', N'
Union all select 'nei', N 'Shanghai' -- tender response
Union all select 'nen', N 'none'
Union all select 'neng', n' neng' -- well, there is a limit.
Union all select 'ni', N 'region'
Union all select 'niance', N 'Shanghai'
Union all select 'niang ', N 'Shanghai'
Union all select 'niao', N 'ure'
Union all select 'ni', N 'region'
Union all select 'nin', n' then'
Union all select 'ning ', N 'hour'
Union all select 'Niu', N 'Shanghai'
Union all select 'Nong ', N 'hangzhou'
Union all select 'nou', N 'hangzhou'
Union all select 'nu ', N 'hour'
Union all select 'nv ', N 'hangzhou'
Union all select 'Nue ', N 'hangzhou'
Union all select 'nuany', N 'hour' -- divide into two values
Union all select 'nuo', n' then'
Union all select 'O', n'hour' -- returns Returns returns
Union all select 'ou ', n' else'
Union all select 'Pa', N 'region'
Union all select 'Pai ', N 'hangzhou' -- Aggregate
Union all select 'Pan', N 'region'
Union all select 'pang ', N 'fat'
Union all select 'pao', N 'pao'
Union all select 'pei ', N 'region'
Union all select 'open', N 'hangzhou'
Union all select 'Peng', N 'hangzhou' -- returns two distinct
Union all select 'Pi', N 'region'
Union all select 'piany', N 'region'
Union all select 'piao', N 'region'
Union all select 'Pie ', N 'married'
Union all select 'pin', n' hire'
Union all select 'ping', N 'hangzhou'
Union all select 'Po', N 'AP'
Union all select 'pou', N 'hangzhou' -- distinct
Union all select 'put', n' exposure'
Union all select 'qy', n' then'
Union all select 'qia ', N 'ili'
Union all select 'Qian ', N 'hangzhou'
Union all select 'Qiang ', N 'hangzhou' -- Aggregate
Union all select 'qiao', N 'hangzhou'
Union all select 'qie', N 'married'
Union all select 'Qin', n' then'
Union all select 'Qing', N 'hangzhou'
Union all select 'qiong ', N 'hangzhou'
Union all select 'Qiu ', n' else'
Union all select 'qu', N 'hangzhou'
Union all select 'quany', n' then'
Union all select 'que', n' else'
Union all select 'qun', n' then'
Union all select 'run', N 'running'
Union all select 'rang ', n' let'
Union all select 'rao', n' then'
Union all select 're', N 'region'
Union all select 'ren', N 'region'
Union all select 'reng', N 'region'
Union all select 'ri', N 'region'
Union all select 'Rong ', N 'hangzhou'
Union all select 'rou', N 'region'
Union all select 'Ru ', N 'hour'
Union all select 'rule', N 'region'
Union all select 'Rui ', N 'Shanghai'
Union all select 'run', N 'running' -- returns success
Union all select 'ruo', n' then'
Union all select 'sa ', N 'hangzhou' -- distinct
Union all select 'Sai ', N 'hangzhou' -- distinct
Union all select 'san', N 'hangzhou'
Union all select 'Shanghai', N 'Shanghai'
Union all select 'sao', n' then'
Union all select 'Se ', N 'hangzhou' -- T-shirt success
Union all select 'sen', N 'region'
Union all select 'seng', N 'hangzhou' -- Aggregate
Union all select 'Sha', n'saha'
Union all select 'Sha', N 'shares'
Union all select 'Sha', N 'shares'
Union all select 'Shanghai', N 'Shanghai'
Union all select 'shao', N 'shares'
Union all select 'she', N 'region'
Union all select 'shen', N 'Shanghai'
Union all select 'Shanghai', N 'Shanghai'
Union all select 'Shi ', N 'Shanghai' -- returns Returns returns
Union all select 'Shou', N 'hangzhou'
Union all select 'shu', N 'Shanghai'
Union all select 'shua ', N 'Shanghai'
Union all select 'shuai', N 'Shanghai'
Union all select 'shuance', N 'hangzhou'
Union all select 'shuang ', N 'hangzhou'
Union all select 'Shanghai', N 'sleep'
Union all select 'Shanghai', N 'Shanghai'
Union all select 'Shanghai', N 'Shanghai'
Union all select 'Si', N 'hangzhou' -- Aggregate
Union all select 'song', N 'hangzhou'
Union all select 'sou', N 'hangzhou'
Union all select 'Su ', N 'hangzhou'
Union all select 'suance', N 'count'
Union all select 'sui', n' then'
Union all select 'sun', N 'Shanghai'
Union all select 'suo', n' then'
Union all select 'ta ', N 'Taobao' -- distinct
Union all select 'Tai', N 'Tai'
Union all select 'Tan ', n' else'
Union all select 'Shanghai', N 'Shanghai'
Union all select 'ta', N 'Taobao' -- Aggregate
Union all select 'Te', N 'hour'
Union all select 'teng', N 'hangzhou' -- limit
Union all select 'ti', N 'region'
Union all select 'tiany', N 'region'
Union all select 'tiao', N 'region'
Union all select 'tie ', N 'region'
Union all select 'ting', N 'hangzhou' -- distinct
Union all select 'tong', N 'hangzhou'
Union all select 'tou', N 'passthrough'
Union all select 'tu', N 'region'
Union all select 'tuany', n' then'
Union all select 'Tui ', N 'Shanghai'
Union all select 'tun, N 'running'
Union all select 'tuo', n' then'
Union all select 'wa ', n' else'
Union all select 'wai', N 'hangzhou'
Union all select 'wan ', N 'hangzhou'
Union all select 'Wang ', N 'hangzhou'
Union all select 'Wei', N 'hangzhou'
Union all select 'wen', N 'hangzhou'
Union all select 'engg', N 'hangzhou'
Union all select 'wo', n' then'
Union all select 'wu', N 'hangzhou'
Union all select 'Xi ', n' else'
Union all select 'CIA', N 'region'
Union all select 'xian ', n' else'
Union all select 'Shanghai', N 'Shanghai'
Union all select 'Xiao', N 'Shanghai'
Union all select 'xie', N 'Shanghai'
Union all select 'sin', N 'region'
Union all select 'X', N 'hangzhou'
Union all select 'xiong ', N 'hangzhou'
Union all select 'Xiu', N 'Shanghai'
Union all select 'Xu ', N 'hangzhou'
Union all select 'Shanghai', N 'Shanghai'
Union all select 'xue ', n' else'
Union all select 'xun ', N 'hangzhou'
Union all select 'ya ', n' else'
Union all select 'any', n' then'
Union all select 'Yang', N '-like'
Union all select 'yunao', N 'hangzhou'
Union all select 'Ye ', N 'hour' -- contains two rows
Union all select 'yi', N 'Shanghai'
Union all select 'yin', n' then'
Union all select 'ying', N 'hangzhou'
Union all select 'Yo', N 'hour'
Union all select 'Yong ', N 'hangzhou'
Union all select 'you', N 'ferr'
Union all select 'Yu ', N 'Yu'
Union all select 'yuanyuan ', N 'Shanghai'
Union all select 'yue', N 'hangzhou'
Union all select 'yun ', N 'hangzhou'
Union all select 'za', n' then'
Union all select 'zai', N 'Shanghai'
Union all select 'zany', n' then'
Union all select 'zang ', N 'hangzhou'
Union all select 'zao', N 'Shanghai'
Union all select 'Z', N 'hour'
Union all select 'Zei', n' then'
Union all select 'zn', N 'hangzhou'
Union all select 'zeng', n' then'
Union all select 'zha', N 'Shanghai'
Union all select 'Zhai', N 'Shanghai'
Union all select 'zhan ', N 'Shanghai'
Union all select 'Zhang ', N 'hangzhou'
Union all select 'zhao', n' then'
Union all select 'zhes', n' then'
Union all select 'Zhen', N 'hangzhou'
Union all select 'zheng', n' certificate'
Union all select 'zhi', N 'Shanghai'
Union all select 'Zhong ', N 'hangzhou'
Union all select 'zhou', n' else'
Union all select 'zhu', N 'hangzhou'
Union all select 'zhua', n' claw'
Union all select 'zhuai', N 'Shanghai'
Union all select 'zhuany', n' then'
Union all select 'zhuang ', N 'Shanghai'
Union all select 'zhui', n' then'
Union all select 'zhun', n' then'
Union all select 'zhuo', n' then'
Union all select 'zi', N 'hangzhou' -- distinct
Union all select 'zong ', N 'hangzhou'
Union all select 'zou', N 'hour'
Union all select 'Zu', N 'hangzhou'
Union all select 'zuany', N 'hangzhou'
Union all select 'zui ', N 'Shanghai'
Union all select 'Zun ', N 'hangzhou'
Union all select 'zuo', n' then ') t
Where word >=@ word collate Chinese_PRC_CS_AS_KS_WS
Order by word ASC) else @ word end)
Set @ I = @ I + 1
End
Return @ pinyin
END
GO

SELECT dbo. fn_GetPinyin ('People's Republic of China ')

/*

-------------------------------------------
Zuo zhong hua ren min gong he guo

(One row is affected)
*/

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.