SQLserver中获取文字的全拼音：
1 CREATE function [dbo].[f_GetPinyin](@words nvarchar(2000)) 2 returns varchar(8000) 3 as 4 begin 5 declare @word nchar(1) 6 declare @pinyin varchar(8000) 7 declare @i int 8 declare @words_len int 9 declare @unicode int 10 set @i = 1 11 set @words = ltrim(rtrim(@words)) 12 set @words_len = len(@words) 13 while (@i <= @words_len) --循环取字符 14 begin 15 set @word = substring(@words, @i, 1) 16 set @unicode = unicode(@word) 17 set @pinyin = ISNULL(@pinyin,'')+ 18 (case when unicode(@word) between 19968 and 19968+20901 then 19 (select top 1 py from ( 20 select 'a' as py,N'厑' as word 21 union all select 'ai',N'靉' 22 union all select 'an',N'黯' 23 union all select 'ang',N'醠' 24 union all select 'ao',N'驁' 25 union all select 'ba',N'欛' 26 union all select 'bai',N'瓸' 27 union all select 'ban',N'瓣' 28 union all select 'bang',N'鎊' 29 union all select 'bao',N'鑤' 30 union all select 'bei',N'鐾' 31 union all select 'ben',N'輽' 32 union all select 'beng',N'鏰' 33 union all select 'bi',N'鼊' 34 union all select 'bian',N'變' 35 union all select 'biao',N'鰾' 36 union all select 'bie',N'彆' 37 union all select 'bin',N'鬢' 38 union all select 'bing',N'靐' 39 union all select 'bo',N'蔔' 40 union all select 'bu',N'簿' 41 union all select 'ca',N'囃' 42 union all select 'cai',N'乲' 43 union all select 'can',N'爘' 44 union all select 'cang',N'賶' 45 union all select 'cao',N'鼜' 46 union all select 'ce',N'簎' 47 union all select 'cen',N'笒' 48 union all select 'ceng',N'乽' 49 union all select 'cha',N'詫' 50 union all select 'chai',N'囆' 51 union all select 'chan',N'顫' 52 union all select 'chang',N'韔' 53 union all select 'chao',N'觘' 54 union all select 'che',N'爡' 55 union all select 'chen',N'讖' 56 union all select 'cheng',N'秤' 57 union all select 'chi',N'鷘' 58 union all select 'chong',N'銃' 59 union all select 'chou',N'殠' 60 union all select 'chu',N'矗' 61 union all select 'chuai',N'踹' 62 union all select 'chuan',N'鶨' 63 union all select 'chuang',N'愴' 64 union all select 'chui',N'顀' 65 union all select 'chun',N'蠢' 66 union all select 'chuo',N'縒' 67 union all select 'ci',N'嗭' 68 union all select 'cong',N'謥' 69 union all select 'cou',N'輳' 70 union all select 'cu',N'顣' 71 union all select 'cuan',N'爨' 72 union all select 'cui',N'臎' 73 union all select 'cun',N'籿' 74 union all select 'cuo',N'錯' 75 union all select 'da',N'橽' 76 union all select 'dai',N'靆' 77 union all select 'dan',N'饏' 78 union all select 'dang',N'闣' 79 union all select 'dao',N'纛' 80 union all select 'de',N'的' 81 union all select 'den',N'扽' 82 union all select 'deng',N'鐙' 83 union all select 'di',N'螮' 84 union all select 'dia',N'嗲' 85 union all select 'dian',N'驔' 86 union all select 'diao',N'鑃' 87 union all select 'die',N'嚸' 88 union all select 'ding',N'顁' 89 union all select 'diu',N'銩' 90 union all select 'dong',N'霘' 91 union all select 'dou',N'鬭' 92 union all select 'du',N'蠹' 93 union all select 'duan',N'叾' 94 union all select 'dui',N'譵' 95 union all select 'dun',N'踲' 96 union all select 'duo',N'鵽' 97 union all select 'e',N'鱷' 98 union all select 'en',N'摁' 99 union all select 'eng',N'鞥' 100 union all select 'er',N'樲' 101 union all select 'fa',N'髮' 102 union all select 'fan',N'瀪' 103 union all select 'fang',N'放' 104 union all select 'fei',N'靅' 105 union all select 'fen',N'鱝' 106 union all select 'feng',N'覅' 107 union all select 'fo',N'梻' 108 union all select 'fou',N'鴀' 109 union all select 'fu',N'猤' 110 union all select 'ga',N'魀' 111 union all select 'gai',N'瓂' 112 union all select 'gan',N'灨' 113 union all select 'gang',N'戇' 114 union all select 'gao',N'鋯' 115 union all select 'ge',N'獦' 116 union all select 'gei',N'給' 117 union all select 'gen',N'搄' 118 union all select 'geng',N'堩' 119 union all select 'gong',N'兣' 120 union all select 'gou',N'購' 121 union all select 'gu',N'顧' 122 union all select 'gua',N'詿' 123 union all select 'guai',N'恠' 124 union all select 'guan',N'鱹' 125 union all select 'guang',N'撗' 126 union all select 'gui',N'鱥' 127 union all select 'gun',N'謴' 128 union all select 'guo',N'腂' 129 union all select 'ha',N'哈' 130 union all select 'hai',N'饚' 131 union all select 'han',N'鶾' 132 union all select 'hang',N'沆' 133 union all select 'hao',N'兞' 134 union all select 'he',N'靏' 135 union all select 'hei',N'嬒' 136 union all select 'hen',N'恨' 137 union all select 'heng',N'堼' 138 union all select 'hong',N'鬨' 139 union all select 'hou',N'鱟' 140 union all select 'hu',N'鸌' 141 union all select 'hua',N'蘳' 142 union all select 'huai',N'蘾' 143 union all select 'huan',N'鰀' 144 union all select 'huang',N'鎤' 145 union all select 'hui',N'顪' 146 union all select 'hun',N'諢' 147 union all select 'huo',N'夻' 148 union all select 'ji',N'驥' 149 union all select 'jia',N'嗧' 150 union all select 'jian',N'鑳' 151 union all select 'jiang',N'謽' 152 union all select 'jiao',N'釂' 153 union all select 'jie',N'繲' 154 union all select 'jin',N'齽' 155 union all select 'jing',N'竸' 156 union all select 'jiong',N'蘔' 157 union all select 'jiu',N'欍' 158 union all select 'ju',N'爠' 159 union all select 'juan',N'羂' 160 union all select 'jue',N'钁' 161 union all select 'jun',N'攈' 162 union all select 'ka',N'鉲' 163 union all select 'kai',N'乫' 164 union all select 'kan',N'矙' 165 union all select 'kang',N'閌' 166 union all select 'kao',N'鯌' 167 union all select 'ke',N'騍' 168 union all select 'ken',N'褃' 169 union all select 'keng',N'鏗' 170 union all select 'kong',N'廤' 171 union all select 'kou',N'鷇' 172 union all select 'ku',N'嚳' 173 union all select 'kua',N'骻' 174 union all select 'kuai',N'鱠' 175 union all select 'kuan',N'窾' 176 union all select 'kuang',N'鑛' 177 union all select 'kui',N'鑎' 178 union all select 'kun',N'睏' 179 union all select 'kuo',N'穒' 180 union all select 'la',N'鞡' 181 union all select 'lai',N'籟' 182 union all select 'lan',N'糷' 183 union all select 'lang',N'唥' 184 union all select 'lao',N'軂' 185 union all select 'le',N'餎' 186 union all select 'lei',N'脷' 187 union all select 'leng',N'睖' 188 union all select 'li',N'瓈' 189 union all select 'lia',N'倆' 190 union all select 'lian',N'纞' 191 union all select 'liang',N'鍄' 192 union all select 'liao',N'瞭' 193 union all select 'lie',N'鱲' 194 union all select 'lin',N'轥' 195 union all select 'ling',N'炩' 196 union all select 'liu',N'咯' 197 union all select 'long',N'贚' 198 union all select 'lou',N'鏤' 199 union all select 'lu',N'氇' 200 union all select 'lv',N'鑢' 201 union all select 'luan',N'亂' 202 union all select 'lue',N'擽' 203 union all select 'lun',N'論' 204 union all select 'luo',N'鱳' 205 union all select 'ma',N'嘛' 206 union all select 'mai',N'霢' 207 union all select 'man',N'蘰' 208 union all select 'mang',N'蠎' 209 union all select 'mao',N'唜' 210 union all select 'me',N'癦' 211 union all select 'mei',N'嚜' 212 union all select 'men',N'們' 213 union all select 'meng',N'霥' 214 union all select 'mi',N'羃' 215 union all select 'mian',N'麵' 216 union all select 'miao',N'廟' 217 union all select 'mie',N'鱴' 218 union all select 'min',N'鰵' 219 union all select 'ming',N'詺' 220 union all select 'miu',N'謬' 221 union all select 'mo',N'耱' 222 union all select 'mou',N'麰' 223 union all select 'mu',N'旀' 224 union all select 'na',N'魶' 225 union all select 'nai',N'錼' 226 union all select 'nan',N'婻' 227 union all select 'nang',N'齉' 228 union all select 'nao',N'臑' 229 union all select 'ne',N'呢' 230 union all select 'nei',N'焾' 231 union all select 'nen',N'嫩' 232 union all select 'neng',N'能' 233 union all select 'ni',N'嬺' 234 union all select 'nian',N'艌' 235 union all select 'niang',N'釀' 236 union all select 'niao',N'脲' 237 union all select 'nie',N'钀' 238 union all select 'nin',N'拰' 239 union all select 'ning',N'濘' 240 union all select 'niu',N'靵' 241 union all select 'nong',N'齈' 242 union all select 'nou',N'譳' 243 union all select 'nu',N'搙' 244 union all select 'nv',N'衄' 245 union all select 'nue',N'瘧' 246 union all select 'nuan',N'燶' 247 union all select 'nuo',N'桛' 248 union all select 'o',N'鞰' 249 union all select 'ou',N'漚' 250 union all select 'pa',N'袙' 251 union all select 'pai',N'磗' 252 union all select 'pan',N'鑻' 253 union all select 'pang',N'胖' 254 union all select 'pao',N'礮' 255 union all select 'pei',N'轡' 256 union all select 'pen',N'喯' 257 union all select 'peng',N'喸' 258 union all select 'pi',N'鸊' 259 union all select 'pian',N'騙' 260 union all select 'piao',N'慓' 261 union all select 'pie',N'嫳' 262 union all select 'pin',N'聘' 263 union all select 'ping',N'蘋' 264 union all select 'po',N'魄' 265 union all select 'pou',N'哛' 266 union all select 'pu',N'曝' 267 union all select 'qi',N'蟿' 268 union all select 'qia',N'髂' 269 union all select 'qian',N'縴' 270 union all select 'qiang',N'瓩' 271 union all select 'qiao',N'躈' 272 union all select 'qie',N'籡' 273 union all select 'qin',N'藽' 274 union all select 'qing',N'櫦' 275 union all select 'qiong',N'瓗' 276 union all select 'qiu',N'糗' 277 union all select 'qu',N'覻' 278 union all select 'quan',N'勸' 279 union all select 'que',N'礭' 280 union all select 'qun',N'囕' 281 union all select 'ran',N'橪' 282 union all select 'rang',N'讓' 283 union all select 'rao',N'繞' 284 union all select 're',N'熱' 285 union all select 'ren',N'餁' 286 union all select 'reng',N'陾' 287 union all select 'ri',N'馹' 288 union all select 'rong',N'穃' 289 union all select 'rou',N'嶿' 290 union all select 'ru',N'擩' 291 union all select 'ruan',N'礝' 292 union all select 'rui',N'壡' 293 union all select 'run',N'橍' 294 union all select 'ruo',N'鶸' 295 union all select 'sa',N'栍' 296 union all select 'sai',N'虄' 297 union all select 'san',N'閐' 298 union all select 'sang',N'喪' 299 union all select 'sao',N'髞' 300 union all select 'se',N'飋' 301 union all select 'sen',N'篸' 302 union all select 'seng',N'縇' 303 union all select 'sha',N'霎' 304 union all select 'shai',N'曬' 305 union all select 'shan',N'鱔' 306 union all select 'shang',N'緔' 307 union all select 'shao',N'潲' 308 union all select 'she',N'欇' 309 union all select 'shen',N'瘮' 310 union all select 'sheng',N'賸' 311 union all select 'shi',N'瓧' 312 union all select 'shou',N'鏉' 313 union all select 'shu',N'虪' 314 union all select 'shua',N'誜' 315 union all select 'shuai',N'卛' 316 union all select 'shuan',N'腨' 317 union all select 'shuang',N'灀' 318 union all select 'shui',N'睡' 319 union all select 'shun',N'鬊' 320 union all select 'shuo',N'鑠' 321 union all select 'si',N'乺' 322 union all select 'song',N'鎹' 323 union all select 'sou',N'瘶' 324 union all select 'su',N'鷫' 325 union all select 'suan',N'算' 326 union all select 'sui',N'鐩' 327 union all select 'sun',N'潠' 328 union all select 'suo',N'蜶' 329 union all select 'ta',N'襨' 330 union all select 'tai',N'燤' 331 union all select 'tan',N'賧' 332 union all select 'tang',N'燙' 333 union all select 'tao',N'畓' 334 union all select 'te',N'蟘' 335 union all select 'teng',N'朰' 336 union all select 'ti',N'趯' 337 union all select 'tian',N'舚' 338 union all select 'tiao',N'糶' 339 union all select 'tie',N'餮' 340 union all select 'ting',N'乭' 341 union all select 'tong',N'憅' 342 union all select 'tou',N'透' 343 union all select 'tu',N'鵵' 344 union all select 'tuan',N'褖' 345 union all select 'tui',N'駾' 346 union all select 'tun',N'坉' 347 union all select 'tuo',N'籜' 348 union all select 'wa',N'韤' 349 union all select 'wai',N'顡' 350 union all select 'wan',N'贎' 351 union all select 'wang',N'朢' 352 union all select 'wei',N'躛' 353 union all select 'wen',N'璺' 354 union all select 'weng',N'齆' 355 union all select 'wo',N'齷' 356 union all select 'wu',N'鶩' 357 union all select 'xi',N'衋' 358 union all select 'xia',N'鏬' 359 union all select 'xian',N'鼸' 360 union all select 'xiang',N'鱌' 361 union all select 'xiao',N'斆' 362 union all select 'xie',N'躞' 363 union all select 'xin',N'釁' 364 union all select 'xing',N'臖' 365 union all select 'xiong',N'敻' 366 union all select 'xiu',N'齅' 367 union all select 'xu',N'蓿' 368 union all select 'xuan',N'贙' 369 union all select 'xue',N'瀥' 370 union all select 'xun',N'鑂' 371 union all select 'ya',N'齾' 372 union all select 'yan',N'灩' 373 union all select 'yang',N'樣' 374 union all select 'yao',N'鑰' 375 union all select 'ye',N'岃' 376 union all select 'yi',N'齸' 377 union all select 'yin',N'檼' 378 union all select 'ying',N'譍' 379 union all select 'yo',N'喲' 380 union all select 'yong',N'醟' 381 union all select 'you',N'鼬' 382 union all select 'yu',N'爩' 383 union all select 'yuan',N'願' 384 union all select 'yue',N'鸙' 385 union all select 'yun',N'韻' 386 union all select 'za',N'雥' 387 union all select 'zai',N'縡' 388 union all select 'zan',N'饡' 389 union all select 'zang',N'臟' 390 union all select 'zao',N'竈' 391 union all select 'ze',N'稄' 392 union all select 'zei',N'鱡' 393 union all select 'zen',N'囎' 394 union all select 'zeng',N'贈' 395 union all select 'zha',N'醡' 396 union all select 'zhai',N'瘵' 397 union all select 'zhan',N'驏' 398 union all select 'zhang',N'瞕' 399 union all select 'zhao',N'羄' 400 union all select 'zhe',N'鷓' 401 union all select 'zhen',N'黮' 402 union all select 'zheng',N'證' 403 union all select 'zhi',N'豒' 404 union all select 'zhong',N'諥' 405 union all select 'zhou',N'驟' 406 union all select 'zhu',N'鑄' 407 union all select 'zhua',N'爪' 408 union all select 'zhuai',N'跩' 409 union all select 'zhuan',N'籑' 410 union all select 'zhuang',N'戅' 411 union all select 'zhui',N'鑆' 412 union all select 'zhun',N'稕' 413 union all select 'zhuo',N'籱' 414 union all select 'zi',N'漬' 415 union all select 'zong',N'縱' 416 union all select 'zou',N'媰' 417 union all select 'zu',N'謯' 418 union all select 'zuan',N'攥' 419 union all select 'zui',N'欈' 420 union all select 'zun',N'銌' 421 union all select 'zuo',N'咗') t 422 where word >= @word collate Chinese_PRC_CS_AS_KS_WS 423 order by word ASC) else @word end) 424 set @i = @i + 1 425 end 426 return @pinyin 427 END 428 429 GO
SQLserver中获取单字的全拼音：
1 CREATE function [dbo].[FUN_GETPY](@STR nvarchar(4000)) 2 returns nvarchar(4000) 3 as 4 begin 5 declare @word nchar(1), @PY nvarchar(4000) 6 7 set @PY = '' 8 while len(@str) > 0 9 begin 10 set @word = left(@str, 1) --如果非汉字字符，返回原字符 11 set @PY = @PY + 12 --简体GBK码汉字UNICODE值从19968开始, WINDOWS多国汉字，UNICODE目前收录汉字共20902个 13 (case when unicode(@word) between 19968 and 19968 + 20901 then 14 (select top 1 PY from 15 ( select 'A' as PY, N'驁' as word 16 union all 17 select 'B',N'簿' 18 union all 19 select 'C',N'錯' 20 union all 21 select 'D',N'鵽' 22 union all 23 select 'E',N'樲' 24 union all 25 select 'F',N'鰒' 26 union all 27 select 'G',N'腂' 28 union all 29 select 'H',N'夻' 30 union all 31 select 'J',N'攈' 32 union all 33 select 'K',N'穒' 34 union all 35 select 'L',N'鱳' 36 union all 37 select 'M',N'旀' 38 union all 39 select 'N',N'桛' 40 union all 41 select 'O',N'漚' 42 union all 43 select 'P',N'曝' 44 union all 45 select 'Q',N'囕' 46 union all 47 select 'R',N'鶸' 48 union all 49 select 'S',N'蜶' 50 union all 51 select 'T',N'籜' 52 union all 53 select 'W',N'鶩' 54 union all 55 select 'X',N'鑂' 56 union all 57 select 'Y',N'韻' 58 union all 59 select 'Z',N'咗') T 60 where word >= @word 61 collate Chinese_PRC_CS_AS_KS_WS order by PY ASC) 62 --else @word 63 else (case when CHARINDEX(@word,'-0123456789')>1 and @word <>'' then @word else '' end ) 64 end) 65 set @str = right(@str, len(@str) - 1) 66 end 67 return @PY 68 end 69 GO
SQLserver中通过首字母查询数据：
1 create function f_GetPy(@str nvarchar(4000)) 2 returns nvarchar(4000) 3 as 4 begin 5 declare @strlen int,@re nvarchar(4000) 6 declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1)) 7 insert into @t(chr,letter) 8 select '吖 ', 'A ' union all select '八 ', 'B ' union all 9 select '嚓 ', 'C ' union all select '咑 ', 'D ' union all 10 select '妸 ', 'E ' union all select '发 ', 'F ' union all 11 select '旮 ', 'G ' union all select '铪 ', 'H ' union all 12 select '丌 ', 'J ' union all select '咔 ', 'K ' union all 13 select '垃 ', 'L ' union all select '嘸 ', 'M ' union all 14 select '拏 ', 'N ' union all select '噢 ', 'O ' union all 15 select '妑 ', 'P ' union all select '七 ', 'Q ' union all 16 select '呥 ', 'R ' union all select '仨 ', 'S ' union all 17 select '他 ', 'T ' union all select '屲 ', 'W ' union all 18 select '夕 ', 'X ' union all select '丫 ', 'Y ' union all 19 select '帀 ', 'Z ' 20 select @strlen=len(@str),@re= ' ' 21 while @strlen> 0 22 begin 23 select top 1 @re=letter+@re,@strlen=@strlen-1 24 from @t a where chr <=substring(@str,@strlen,1) 25 order by chr desc 26 if @@rowcount=0 27 select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 28 end 29 return(@re) 30 end 31 32 33 ---查询--- 34 select 35 * 36 from 37 [pactinfo] --表名 38 where 39 left(dbo.f_GetPy(pactname),1)='Z' --可以把1改成任何数字 --pactname 字段
SQLserver中获得文字首字母：
1 create function [dbo].[fun_getPY](@str nvarchar(4000)) 2 returns nvarchar(4000) 3 as 4 begin 5 declare @word nvarchar(1),@PY nvarchar(4000) 6 set @PY='' 7 set @str = ltrim(rtrim(@str)) 8 while len(@str)>0 9 begin 10 set @word=left(@str,1) 11 12 set @PY=@PY+ltrim((case when unicode(@word) between 19968 and 19968+20901 13 then (select top 1 PY from ( 14 select 'A' as PY,N'驁' as word 15 union all select 'B',N'簿' 16 union all select 'C',N'錯' 17 union all select 'D',N'鵽' 18 union all select 'E',N'樲' 19 union all select 'F',N'鰒' 20 union all select 'G',N'腂' 21 union all select 'H',N'夻' 22 union all select 'J',N'攈' 23 union all select 'K',N'穒' 24 union all select 'L',N'鱳' 25 union all select 'M',N'旀' 26 union all select 'N',N'桛' 27 union all select 'O',N'漚' 28 union all select 'P',N'曝' 29 union all select 'Q',N'囕' 30 union all select 'R',N'鶸' 31 union all select 'S',N'蜶' 32 union all select 'T',N'籜' 33 union all select 'W',N'鶩' 34 union all select 'X',N'鑂' 35 union all select 'Y',N'韻' 36 union all select 'Z',N'咗' 37 ) T 38 where word>=@word collate Chinese_PRC_CS_AS_KS_WS 39 order by PY ASC) 40 when unicode(@word) between 8544 and 8552 --希腊字母1-9 41 then nchar(unicode(@word)-8495) 42 when unicode(@word) = 8553 --希腊字母10 43 then '0' 44 when unicode(@word) between 48 and 57 --数字0-9 45 then @word 46 when unicode(@word) between 65296 and 65305 --全角数字0-9 47 then nchar(unicode(@word)-65248) 48 when unicode(upper(@word)) between 65 and 90 --字母a-z和A-Z 49 then upper(@word) 50 when unicode(upper(@word)) between 65313 and 65338 --全角字母a-z和A-Z 51 then nchar(unicode(upper(@word))-65248) 52 else '' end)) --如果非汉字字符或非字母、数字、希腊字母、全角字母、全角数字，返回空字符 53 --set @str=right(@str,len(@str)-1) 54 set @str = substring(@str,2,len(@str)-1) 55 end 56 return @PY 57 end 58 59 --查询 60 select dbo.fun_getPY((SELECT NAME FROM GOODS where ID ='11901')) as result
一般函数的使用方式：
--根据首字母查询
select * from GOODS where left(dbo.f_GetPy(SPECS),2)='20'
--获得字段文字的首字母
select dbo.fun_getPY((SELECT NAME FROM GOODS where ID ='11901')) as result
--获得文字的首字母
select dbo.f_GetPinyin('张三') as result
函数的参数可以换成相应的数据字段