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)
*/
