SQL 取汉字首字母的两个函数
sql提取字段中的数字和字母的函数
sql提取字段中的数字和字母的函数在SQL中，我们经常会遇到需要从字符串字段中提取数字和字母的需求，例如从电话号码字段中提取出数字，或者从地址字段中提取出邮政编码。
为了实现这样的需求，我们可以使用一些内置的SQL 函数来提取字段中的数字和字母。
我们来介绍如何提取字段中的数字。
在SQL中，可以使用函数REGEXP_REPLACE来实现这一功能。
该函数可以通过正则表达式来匹配需要替换的字符，并将其替换为空字符串。
通过将非数字字符替换为空字符串，我们可以实现提取字段中的数字的目的。
例如，假设我们有一个名为phone_number的字段，其中包含了电话号码。
我们可以使用如下的SQL语句来提取出其中的数字：```SELECT REGEXP_REPLACE(phone_number, '[^0-9]', '') AS extracted_numberFROM table_name;```上述SQL语句中，`REGEXP_REPLACE(phone_number, '[^0-9]', '')`表示将phone_number字段中的非数字字符替换为空字符串。
通过将结果命名为extracted_number，我们可以在查询的结果中看到提取出的数字。
接下来，让我们来介绍如何提取字段中的字母。
同样地，我们可以使用REGEXP_REPLACE函数来实现这一功能。
通过将非字母字符替换为空字符串，我们可以提取出字段中的字母。
例如，假设我们有一个名为address的字段，其中包含了地址信息。
我们可以使用如下的SQL语句来提取出其中的字母：```SELECT REGEXP_REPLACE(address, '[^A-Za-z]', '') AS extracted_lettersFROM table_name;```上述SQL语句中，`REGEXP_REPLACE(address, '[^A-Za-z]', '')`表示将address字段中的非字母字符替换为空字符串。
mssql字符串中提取字母的函数
mssql字符串中提取字母的函数在SQL Server中提取字符串中的字母，可以使用多种方法。
下面将介绍几种常见的方法。
1.使用ASCII函数和PATINDEX函数```sqlDECLARE @inputString VARCHAR(100) = 'abcd1234defg56';DECLARE @outputString VARCHAR(100) = '';DECLARE @len INT = LEN(@inputString);DECLARE @index INT = 1;WHILE @index <= @lenBEGINDECLARE @char CHAR(1) = SUBSTRING(@inputString, @index, 1);IF ASCII(@char) BETWEEN 65 AND 90 OR ASCII(@char) BETWEEN 97 AND 122BEGINSET @outputString += @char;ENDSET @index += 1;ENDSELECT @outputString;```上述代码中，首先定义了一个输入字符串变量`@inputString`，并初始化为空字符串`''`。
然后定义了一个输出字符串变量`@outputString`，用于存储提取的字母。
接下来，使用`LEN`函数获取输入字符串的长度，使用`@index`变量循环访问输入字符串的每个字符。
在每个循环中，使用`SUBSTRING`函数提取当前字符，并使用`ASCII`函数获取其ASCII码值。
通过判断ASCII码值是否在字母的范围内（65-90为大写字母，97-122为小写字母），将该字符添加到输出字符串中。
最后，输出结果。
2.使用PATINDEX函数和LIKE语句```sqlDECLARE @inputString VARCHAR(100) = 'abcd1234defg56';DECLARE @outputString VARCHAR(100) = '';WHILE LEN(@inputString) > 0BEGINDECLARE @char CHAR(1) = LEFT(@inputString, 1);IF @char LIKE '[a-zA-Z]'BEGINSET @outputString += @char;ENDSET @inputString = RIGHT(@inputString, LEN(@inputString) - 1);ENDSELECT @outputString;```上述代码中，首先定义了一个输入字符串变量`@inputString`，并初始化为待提取字母的字符串。
sql取汉字首字母的两个函数
go--创建取汉字首字母函数(第一版)create function [dbo].[f_getpy_V1] (@str nvarchar(4000))returns nvarchar(4000)asbegindeclare @word nchar(1),@py nvarchar(4000)set @py=''while len(@str)>0beginset @word=left(@str,1)set @py = @py+(case when unicode(@word)between 19968 and 199 68+20901then(select top 1 pyfrom(select'a'as py, N'驁'as wordunion 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'咗') Twhere word>=@word collate Chinese_PRC_CS_AS_KS_WSorder by py asc)else @wordend)set @str=right(@str,len(@str)-1)endreturn @PYendgo--创建取汉字首字母函数(第二版)create function [dbo].[f_getpy_V2](@Str varchar(500)='') returns varchar(500)asbegindeclare @strlen int,@return varchar(500),@ii intdeclare @n int,@c char(1),@chn nchar(1)select @strlen=len(@str),@return='',@ii=0set @ii=0while @ii<@strlenbeginselect @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)if @chn>'z'select @n = @n +1,@c =case chn when @chn then char(@n)else @c endfrom(select top 27 *from(select chn ='吖'union all select'八'union all select'嚓'union all select'咑'union all select'妸'union all select'发'union all select'旮'union all select'铪'union all select'丌'--because have no 'i'union all select'丌'union all select'咔'union all select'垃'union all select'嘸'union all select'拏'union all select'噢'union all select'妑'union all select'七'union all select'呥'union all select'仨'union all select'他'union all select'屲'--no 'u'union all select'屲'--no 'v'union all select'屲'union all select'夕'union all select'丫'union all select'帀'union all select @chn)as aorder by chn COLLATE Chinese_PRC_CI_AS)as belse set @c='a'set @return=@return+@cendreturn(@return)end--测试示例select dbo.[f_getpy_V1]('王立国')as V11select dbo.[f_getpy_V2]('王立国')as V21select dbo.[f_getpy_V1]('重复')as V21select dbo.[f_getpy_V2]('重复')as V22--运行结果/*V11--------WLGV21--------WLGV21--------ZFV22--------ZF*/--由上我们可以看到，两种方法都没有解决多音字的问题。
sql自动生成汉语拼音和首字母函数
sql⾃动⽣成汉语拼⾳和⾸字母函数1.Sql server⾃动⽣成拼⾳的函数/*根据汉字获取全拼1.⽣成所有读⾳临时表2.根据Chinese_PRC_CS_AS_KS_WS 排序获取读⾳*/CREATE function f_GetPying(@str varchar(100))returns varchar(8000)asbegindeclare@re varchar(8000)--⽣成临时表declare@t table(chr nchar(1) collate Chinese_PRC_CS_AS_KS_WS,py nvarchar(20))insert into@t select'吖','a'insert into@t select'厑','aes'insert into@t select'哎','ai'insert into@t select'安','an'insert into@t select'肮','ang'insert into@t select'凹','ao'insert into@t select'⼋','ba'insert into@t select'挀','bai'insert into@t select'兡','baike'insert into@t select'瓸','baiwa'insert into@t select'扳','ban'insert into@t select'邦','bang'insert into@t select'⼓','bao'insert into@t select'萡','be'insert into@t select'陂','bei'insert into@t select'奔','ben'insert into@t select'伻','beng'insert into@t select'皀','bi'insert into@t select'边','bian'insert into@t select'辪','uu'insert into@t select'灬','biao'insert into@t select'憋','bie'insert into@t select'汃','bin'insert into@t select'⼎','bing'insert into@t select'⽨','bo'insert into@t select'峬','bu'insert into@t select'嚓','ca'insert into@t select'偲','cai'insert into@t select'乲','cal'insert into@t select'参','can'insert into@t select'仓','cang'insert into@t select'撡','cao'insert into@t select'冊','ce'insert into@t select'膥','cen'insert into@t select'噌','ceng'insert into@t select'硛','ceok'insert into@t select'岾','ceom'insert into@t select'猠','ceon'insert into@t select'乽','ceor'insert into@t select'叉','cha'insert into@t select'犲','chai'insert into@t select'辿','chan'insert into@t select'伥','chang'insert into@t select'抄','chao'insert into@t select'车','che'insert into@t select'抻','chen'insert into@t select'阷','cheng'insert into@t select'吃','chi'insert into@t select'充','chong'insert into@t select'抽','chou'insert into@t select'出','chu'insert into@t select'膗','chuai'insert into@t select'⼮','chuan'insert into@t select'刅','chuang'insert into@t select'吹','chui'insert into@t select'旾','chun'insert into@t select'踔','chuo'insert into@t select'呲','ci'insert into@t select'嗭','cis'insert into@t select'从','cong'insert into@t select'凑','cou'insert into@t select'粗','cu'insert into@t select'汆','cuan'insert into@t select'崔','cui'insert into@t select'邨','cun'insert into@t select'瑳','cuo'insert into@t select'撮','chua'insert into@t select'咑','da'insert into@t select'仾','di' insert into@t select'嗲','dia' insert into@t select'敁','dian' insert into@t select'刁','diao' insert into@t select'爹','die' insert into@t select'哋','dei' insert into@t select'嚸','dim' insert into@t select'丁','ding' insert into@t select'丟','diu' insert into@t select'东','dong' insert into@t select'吺','dou' insert into@t select'剢','du' insert into@t select'耑','duan' insert into@t select'叾','dug' insert into@t select'垖','dui' insert into@t select'吨','dun' insert into@t select'咄','duo' insert into@t select'妸','e' insert into@t select'奀','en' insert into@t select'鞥','eng' insert into@t select'仒','eo' insert into@t select'乻','eol' insert into@t select'旕','eos' insert into@t select'⼉','er' insert into@t select'发','fa' insert into@t select'帆','fan' insert into@t select'⼕','fang' insert into@t select'飞','fei' insert into@t select'吩','fen' insert into@t select'丰','feng' insert into@t select'瓰','fenwa' insert into@t select'覅','fiao' insert into@t select'仏','fo' insert into@t select'垺','fou' insert into@t select'夫','fu' insert into@t select'猤','fui' insert into@t select'旮','ga' insert into@t select'侅','gai' insert into@t select'⽢','gan' insert into@t select'冈','gang' insert into@t select'皋','gao' insert into@t select'⼽','ge' insert into@t select'给','gei' insert into@t select'根','gen' insert into@t select'更','geng' insert into@t select'啹','geu' insert into@t select'喼','gib' insert into@t select'嗰','go' insert into@t select'⼯','gong' insert into@t select'兝','gongfen' insert into@t select'兣','gongli' insert into@t select'勾','gou' insert into@t select'估','gu' insert into@t select'⽠','gua' insert into@t select'乖','guai' insert into@t select'关','guan' insert into@t select'光','guang' insert into@t select'归','gui' insert into@t select'⼁','gun' insert into@t select'呙','guo' insert into@t select'妎','ha' insert into@t select'咍','hai' insert into@t select'乤','hal' insert into@t select'兯','han' insert into@t select'魧','hang' insert into@t select'茠','hao' insert into@t select'兞','haoke' insert into@t select'诃','he' insert into@t select'黒','hei' insert into@t select'拫','hen' insert into@t select'亨','heng' insert into@t select'囍','heui' insert into@t select'乊','ho' insert into@t select'乥','hol' insert into@t select'叿','hong' insert into@t select'齁','hou' insert into@t select'乎','hu' insert into@t select'花','hua' insert into@t select'徊','huai' insert into@t select'欢','huan' insert into@t select'巟','huang'insert into@t select'戋','jian' insert into@t select'江','jiang' insert into@t select'艽','jiao' insert into@t select'阶','jie' insert into@t select'⼱','jin' insert into@t select'坕','jing' insert into@t select'⼌','jiong' insert into@t select'丩','jiu' insert into@t select'欍','jou' insert into@t select'凥','ju' insert into@t select'姢','juan' insert into@t select'噘','jue' insert into@t select'军','jun' insert into@t select'咔','ka' insert into@t select'开','kai' insert into@t select'乫','kal' insert into@t select'刊','kan' insert into@t select'冚','hem' insert into@t select'砊','kang' insert into@t select'尻','kao' insert into@t select'坷','ke' insert into@t select'肎','ken' insert into@t select'劥','keng' insert into@t select'巪','keo' insert into@t select'乬','keol' insert into@t select'唟','keos' insert into@t select'厼','keum' insert into@t select'怾','ki' insert into@t select'空','kong' insert into@t select'廤','kos' insert into@t select'抠','kou' insert into@t select'扝','ku' insert into@t select'夸','kua' insert into@t select'蒯','kuai' insert into@t select'宽','kuan' insert into@t select'匡','kuang' insert into@t select'亏','kui' insert into@t select'坤','kun' insert into@t select'拡','kuo' insert into@t select'穒','kweok' insert into@t select'垃','la' insert into@t select'来','lai' insert into@t select'兰','lan' insert into@t select'啷','lang' insert into@t select'捞','lao' insert into@t select'仂','le' insert into@t select'雷','lei' insert into@t select'塄','leng' insert into@t select'唎','li' insert into@t select'俩','lia' insert into@t select'嫾','lian' insert into@t select'簗','liang' insert into@t select'蹽','liao' insert into@t select'毟','lie' insert into@t select'厸','lin' insert into@t select'伶','ling' insert into@t select'溜','liu' insert into@t select'瓼','liwa' insert into@t select'囖','lo' insert into@t select'龙','long' insert into@t select'娄','lou' insert into@t select'噜','lu' insert into@t select'驴','lv' insert into@t select'寽','lue' insert into@t select'孪','luan' insert into@t select'掄','lun' insert into@t select'頱','luo' insert into@t select'呣','m' insert into@t select'妈','ma' insert into@t select'遤','hweong' insert into@t select'埋','mai' insert into@t select'颟','man' insert into@t select'牤','mang' insert into@t select'匁','mangmi' insert into@t select'猫','mao' insert into@t select'唜','mas' insert into@t select'庅','me' insert into@t select'呅','mei' insert into@t select'椚','men' insert into@t select'掹','meng' insert into@t select'踎','meo'insert into@t select'谬','miu' insert into@t select'摸','mo' insert into@t select'乮','mol' insert into@t select'哞','mou' insert into@t select'母','mu' insert into@t select'旀','myeo' insert into@t select'丆','myeon' insert into@t select'椧','myeong' insert into@t select'拏','na' insert into@t select'腉','nai' insert into@t select'囡','nan' insert into@t select'囔','nang' insert into@t select'乪','keg' insert into@t select'孬','nao' insert into@t select'⽧','ne' insert into@t select'娞','nei' insert into@t select'焾','nem' insert into@t select'嫩','nen' insert into@t select'莻','neus' insert into@t select'鈪','ngag' insert into@t select'銰','ngai' insert into@t select'啱','ngam' insert into@t select'妮','ni'insert into@t select'年','nian' insert into@t select'娘','niang' insert into@t select'茑','niao' insert into@t select'捏','nie' insert into@t select'脌','nin' insert into@t select'宁','ning' insert into@t select'⽜','niu' insert into@t select'农','nong' insert into@t select'羺','nou' insert into@t select'奴','nu' insert into@t select'⼥','nv' insert into@t select'疟','nue' insert into@t select'瘧','nve' insert into@t select'奻','nuan' insert into@t select'黁','nun' insert into@t select'燶','nung' insert into@t select'挪','nuo' insert into@t select'筽','o'insert into@t select'夞','oes' insert into@t select'乯','ol'insert into@t select'鞰','on' insert into@t select'讴','ou' insert into@t select'妑','pa' insert into@t select'俳','pai' insert into@t select'磗','pak' insert into@t select'眅','pan' insert into@t select'乓','pang' insert into@t select'抛','pao' insert into@t select'呸','pei' insert into@t select'瓫','pen' insert into@t select'匉','peng' insert into@t select'浌','peol' insert into@t select'巼','phas' insert into@t select'闏','phdeng' insert into@t select'乶','phoi' insert into@t select'喸','phos' insert into@t select'丕','pi'insert into@t select'囨','pian' insert into@t select'缥','piao' insert into@t select'氕','pie' insert into@t select'⼃','pianpang' insert into@t select'姘','pin' insert into@t select'乒','ping' insert into@t select'钋','po' insert into@t select'剖','pou' insert into@t select'哣','deo' insert into@t select'兺','ppun' insert into@t select'仆','pu' insert into@t select'七','qi'insert into@t select'掐','qia' insert into@t select'千','qian' insert into@t select'羌','qiang' insert into@t select'兛','qianke' insert into@t select'瓩','qianwa' insert into@t select'悄','qiao' insert into@t select'苆','qie' insert into@t select'亲','qin' insert into@t select'蠄','kem'insert into@t select'亽','ra' insert into@t select'囕','ram' insert into@t select'呥','ran' insert into@t select'穣','rang' insert into@t select'荛','rao' insert into@t select'惹','re' insert into@t select'⼈','ren' insert into@t select'扔','reng' insert into@t select'⽇','ri' insert into@t select'栄','rong' insert into@t select'⽱','rou' insert into@t select'嶿','ru' insert into@t select'撋','ruan' insert into@t select'桵','rui' insert into@t select'闰','run' insert into@t select'叒','ruo' insert into@t select'仨','sa' insert into@t select'栍','saeng' insert into@t select'毢','sai' insert into@t select'虄','sal' insert into@t select'三','san' insert into@t select'桒','sang' insert into@t select'掻','sao' insert into@t select'⾊','se' insert into@t select'裇','sed' insert into@t select'聓','sei' insert into@t select'森','sen' insert into@t select'鬙','seng' insert into@t select'閪','seo' insert into@t select'縇','seon' insert into@t select'杀','sha' insert into@t select'筛','shai' insert into@t select'⼭','shan' insert into@t select'伤','shang' insert into@t select'弰','shao' insert into@t select'奢','she' insert into@t select'申','shen' insert into@t select'升','sheng' insert into@t select'⼫','shi' insert into@t select'兙','shike' insert into@t select'瓧','shiwa' insert into@t select'収','shou' insert into@t select'书','shu' insert into@t select'刷','shua' insert into@t select'摔','shuai' insert into@t select'闩','shuan' insert into@t select'双','shuang' insert into@t select'谁','shei' insert into@t select'脽','shui' insert into@t select'吮','shun' insert into@t select'哾','shuo' insert into@t select'丝','si' insert into@t select'螦','so' insert into@t select'乺','sol' insert into@t select'忪','song' insert into@t select'凁','sou' insert into@t select'苏','su' insert into@t select'痠','suan' insert into@t select'⼢','sui' insert into@t select'孙','sun' insert into@t select'娑','suo' insert into@t select'他','ta' insert into@t select'襨','tae' insert into@t select'囼','tai' insert into@t select'坍','tan' insert into@t select'铴','tang' insert into@t select'仐','tao' insert into@t select'畓','tap' insert into@t select'忒','te' insert into@t select'膯','teng' insert into@t select'唞','teo' insert into@t select'朰','teul' insert into@t select'剔','ti' insert into@t select'天','tian' insert into@t select'旫','tiao' insert into@t select'怗','tie' insert into@t select'厅','ting' insert into@t select'乭','tol' insert into@t select'囲','tong' insert into@t select'偷','tou' insert into@t select'凸','tu'insert into@t select'危','wei' insert into@t select'塭','wen' insert into@t select'翁','weng' insert into@t select'挝','wo' insert into@t select'乌','wu' insert into@t select'⼣','xi' insert into@t select'诶','ei' insert into@t select'疨','xia' insert into@t select'仙','xian' insert into@t select'乡','xiang' insert into@t select'灱','xiao' insert into@t select'楔','xie' insert into@t select'⼼','xin' insert into@t select'星','xing' insert into@t select'凶','xiong' insert into@t select'休','xiu' insert into@t select'旴','xu' insert into@t select'昍','xuan' insert into@t select'疶','xue' insert into@t select'坃','xun' insert into@t select'丫','ya' insert into@t select'咽','yan' insert into@t select'欕','eom' insert into@t select'央','yang' insert into@t select'吆','yao' insert into@t select'椰','ye' insert into@t select'膶','yen' insert into@t select'⼀','yi' insert into@t select'乁','i' insert into@t select'乚','yin' insert into@t select'应','ying' insert into@t select'哟','yo' insert into@t select'佣','yong' insert into@t select'优','you' insert into@t select'迂','yu' insert into@t select'囦','yuan' insert into@t select'⽈','yue' insert into@t select'蒀','yun' insert into@t select'帀','za' insert into@t select'災','zai' insert into@t select'兂','zan' insert into@t select'牂','zang' insert into@t select'遭','zao' insert into@t select'啫','ze' insert into@t select'贼','zei' insert into@t select'怎','zen' insert into@t select'曽','zeng' insert into@t select'吒','zha' insert into@t select'甴','gad' insert into@t select'夈','zhai' insert into@t select'毡','zhan' insert into@t select'张','zhang' insert into@t select'钊','zhao' insert into@t select'蜇','zhe' insert into@t select'贞','zhen' insert into@t select'凧','zheng' insert into@t select'之','zhi' insert into@t select'中','zhong' insert into@t select'州','zhou' insert into@t select'劯','zhu' insert into@t select'抓','zhua' insert into@t select'专','zhuan' insert into@t select'转','zhuai' insert into@t select'妆','zhuang' insert into@t select'骓','zhui' insert into@t select'宒','zhun' insert into@t select'卓','zhuo' insert into@t select'孜','zi' insert into@t select'唨','zo' insert into@t select'宗','zong' insert into@t select'棸','zou' insert into@t select'哫','zu' insert into@t select'劗','zuan' insert into@t select'厜','zui' insert into@t select'尊','zun' insert into@t select'昨','zuo' declare@strlen intselect@strlen=len(@str),@re='' while@strlen>0beginselect@re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1 endreturn(@re)end测试：SELECT dbo.fn_GetPying('中⽂字段') AS pymc2.sql server⾃动⽣成拼⾳⾸字母的函数--⽣成拼⾳⾸码CREATE function fn_GetPy(@str nvarchar(4000))returns nvarchar(4000)--WITH ENCRYPTIONasbegindeclare@intLen intdeclare@strRet nvarchar(4000)declare@temp nvarchar(100)set@intLen=len(@str)set@strRet=''while@intLen>0beginset@temp=''select@temp=casewhen substring(@str,@intLen,1) >='帀'then'Z'when substring(@str,@intLen,1) >='丫'then'Y'when substring(@str,@intLen,1) >='⼣'then'X'when substring(@str,@intLen,1) >='屲'then'W'when substring(@str,@intLen,1) >='他'then'T'when substring(@str,@intLen,1) >='仨'then'S'when substring(@str,@intLen,1) >='呥'then'R'when substring(@str,@intLen,1) >='七'then'Q'when substring(@str,@intLen,1) >='妑'then'P'when substring(@str,@intLen,1) >='噢'then'O'when substring(@str,@intLen,1) >='拏'then'N'when substring(@str,@intLen,1) >='嘸'then'M'when substring(@str,@intLen,1) >='垃'then'L'when substring(@str,@intLen,1) >='咔'then'K'when substring(@str,@intLen,1) >='丌'then'J'when substring(@str,@intLen,1) >='铪'then'H'when substring(@str,@intLen,1) >='旮'then'G'when substring(@str,@intLen,1) >='发'then'F'when substring(@str,@intLen,1) >='妸'then'E'when substring(@str,@intLen,1) >='咑'then'D'when substring(@str,@intLen,1) >='嚓'then'C'when substring(@str,@intLen,1) >='⼋'then'B'when substring(@str,@intLen,1) >='吖'then'A'else rtrim(ltrim(substring(@str,@intLen,1)))end--对于汉字特殊字符，不⽣成拼⾳码if (ascii(@temp)>127) set@temp=''--对于英⽂中⼩括号，不⽣成拼⾳码if@temp='('or@temp=')'set@temp=''select@strRet=@temp+@strRetset@intLen=@intLen-1endreturn lower(@strRet)end测试：SELECT dbo.fn_GetPy('中⽂字段') AS pymc。
sql存储过程获取汉字拼音头字母函数
sql存储过程获取汉字拼⾳头字母函数复制代码代码如下:--函数CREATE function fn_GetPy(@str nvarchar(4000))returns nvarchar(4000)--WITH ENCRYPTIONasbegindeclare @intLenintdeclare @strRetnvarchar(4000)declare @temp nvarchar(100)set @intLen = len(@str)set @strRet = ''while @intLen > 0beginset @temp = ''select @temp = casewhen substring(@str,@intLen,1) >= '帀' then 'Z'when substring(@str,@intLen,1) >= '丫' then 'Y'when substring(@str,@intLen,1) >= '⼣' then 'X'when substring(@str,@intLen,1) >= '屲' then 'W'when substring(@str,@intLen,1) >= '他' then 'T'when substring(@str,@intLen,1) >= '仨' then 'S'when substring(@str,@intLen,1) >= '呥' then 'R'when substring(@str,@intLen,1) >= '七' then 'Q'when substring(@str,@intLen,1) >= '妑' then 'P'when substring(@str,@intLen,1) >= '噢' then 'O'when substring(@str,@intLen,1) >= '拏' then 'N'when substring(@str,@intLen,1) >= '嘸' then 'M'when substring(@str,@intLen,1) >= '垃' then 'L'when substring(@str,@intLen,1) >= '咔' then 'K'when substring(@str,@intLen,1) >= '丌' then 'J'when substring(@str,@intLen,1) >= '铪' then 'H'when substring(@str,@intLen,1) >= '旮' then 'G'when substring(@str,@intLen,1) >= '发' then 'F'when substring(@str,@intLen,1) >= '妸' then 'E'when substring(@str,@intLen,1) >= '咑' then 'D'when substring(@str,@intLen,1) >= '嚓' then 'C'when substring(@str,@intLen,1) >= '⼋' then 'B'when substring(@str,@intLen,1) >= '吖' then 'A'else rtrim(ltrim(substring(@str,@intLen,1)))end--对于汉字特殊字符，不⽣成拼⾳码if (ascii(@temp)>127) set @temp = ''--对于英⽂中⼩括号，不⽣成拼⾳码if @temp = '(' or @temp = ')' set @temp = ''select @strRet = @temp + @strRetset @intLen = @intLen - 1endreturn lower(@strRet)endgo--调⽤select dbo.fn_getpy('张三')--返回：zs答！： 2:取汉字拼⾳⾸字母的存储过程Create function fun_getPY ( @str nvarchar(4000) )returns nvarchar(4000)asbegindeclare @word nchar(1),@PY nvarchar(4000)set @PY=''while len(@str)>0beginset @word=left(@str,1)--如果⾮汉字字符，返回原字符set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 then (select top 1 PYfrom(select 'A' as PY,N'驁' as wordunion 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'咗') Twhere word>=@word collate Chinese_PRC_CS_AS_KS_WSorder by PY ASC)else @wordend)set @str=right(@str,len(@str)-1)endreturn @PYend。
汉字首字母提取函数公式
汉字首字母提取函数公式
汉字首字母提取函数公式是用来将汉字转化为拼音首字母的数
学公式。
其主要思想是根据汉字的发音规律，将每个汉字的首字母提取出来，然后组成拼音首字母。
公式如下：
def get_first_letter(str):
result = ''
for s in str:
if s >= u'u4e00' and s <= u'u9fa5':
result += chr((ord(s) - 19968) // 676 + 65)
else:
result += s.upper()
return result
其中，参数str表示需要提取首字母的汉字字符串，函数首先定义一个空字符串result用于保存最终的结果。
然后，通过for循环遍历每个汉字，判断它是否在指定的Unicode范围内，如果是，则计算出它的首字母，并将其加入到结果中。
如果不是汉字，则直接将其转化为大写字母，并加入到结果中。
需要注意的是，该函数只适用于简体中文汉字，对于繁体中文或其他语言的汉字可能不适用。
此外，由于汉字的发音规律比较复杂，该函数的准确性也有限，需要根据具体情况进行调整。
- 1 -。
Sql得到（去除）字符串中所有汉字，字母，数字的函数
Sql得到（去除）字符串中所有汉字，字母，数字的函数核⼼思想在于下⾯⼏个模式的组合使⽤：%[0-9]% ：所有数字%[A-Za-z]%:所有字母%[吖-咗]%:所有汉字1. 去除汉字(字母、数字)：举出⼀例，其他类似：CREATE FUNCTION f_RemoveChinese(@str VARCHAR(500))RETURNS VARCHAR(500)ASBEGIN-- '%[0-9]%' 所有數字-- '%[A-Za-z]%' 所有字母-- '%[A-Za-z0-9]%' 所有數字與字母-- .... 組合使⽤，依此類推WHILE PATINDEX('%[吖-咗]%',@str) > 0SET @str = STUFF(@str,PATINDEX('%[吖-咗]%',@str),1,'')RETURN @strENDGOSELECT dbo.f_RemoveChinese('愛@@情123騙⼦我問@@你') -- @@123@@2. 提取汉字(字母、数字)：提取XX可转化为去除⾮XX 。
举出⼀例，其他类似CREATE FUNCTION f_getChinese(@str VARCHAR(500))RETURNS VARCHAR(500)ASBEGIN-- '%[^0-9]%' 所有數字-- '%[^A-Za-z]%' 所有字母-- '%[^A-Za-z0-9]%' 所有數字與字母-- .... 組合使⽤，依此類推WHILE PATINDEX('%[^吖-咗]%',@str) > 0SET @str = STUFF(@str,PATINDEX('%[^吖-咗]%',@str),1,'')RETURN @strENDGOSELECT dbo.f_getChinese('愛@@情123騙⼦我問@@你') -- 愛情騙⼦我問你3. 提取数字（⽀持⼩数点）：上⾯的代码将模式直接换成'%[^0-9]%'的话，不⽀持带⼩数点的数字，下⾯给出⼀个修正的：-- 提取所有漢字（字母、數字）CREATE FUNCTION f_GetNum(@str VARCHAR(500))RETURNS VARCHAR(500)ASBEGIN-- get num and dotWHILE (PATINDEX('%[^0-9.]%',@str) > 0)SET @str = STUFF(@str,PATINDEX('%[^0-9.]%',@str),1,'')-- remove left dotWHILE(LEFT(@str,1)='.')SET @str = RIGHT(@str,LEN(@str)-1)-- remove right dotWHILE(RIGHT(@str,1)='.')SET @str = LEFT(@str,LEN(@str)-1)RETURN @strENDGOSELECT dbo.f_GetNum('愛@@..情1.23騙⼦我問...@@你') -- 1.23。
通过SQLServer取出汉字的拼音
SQL函数实现用拼音对汉字模糊查询方法一：create function f_GetPy(@Str nvarchar(400>> returns nvarchar(4000> asbegindeclare @strlen int,@re nvarchar(4000> declare @t table(chr nchar(1> collate Chinese_PRC_CI_AS,letter nchar(1>> insert @t select '吖','A' union all select '八','B' union all select '嚓','C' union all select '咑','D' union all select '妸','E' union all select '发','F' union all select '旮','G' union all select '铪','H' union all select '丌','J' union all select '咔','K' union all select '垃','L' union all select '嘸','M' union all select '拏','N' union all select '噢','O' union all select '妑','P' union all select '七','Q' union all select '呥','R' union all select '仨','S' union all select '他','T' union all select '屲','W' union all select '夕','X' union all select '丫','Y' union all select '帀','Z'select @strlen=len(@str>,@re=''while @strlen>0beginselect top 1 @re=letter+@re,@strlen=@strlen-1from @t a where chr<=substring(@str,@strlen,1>order by chr descif @@rowcount=0select @re=substring(@str,@strlen,1>+@re,@strlen=@strlen-1 endreturn(@re>endgo--测试select dbo.f_GetPy('东莞市'> as 东莞市,dbo.f_GetPy('ab中c 国人'> as 中国人b5E2RGbCAP--以后查询的时候,就可以调用上面的函数来实现汉字模糊查询select * from 表 where dbo.f_getpy(字段>='zgyh'方法二：CREATE FUNCTION f_GetPY(@str nvarchar(4000>>RETURNS nvarchar(4000>ASBEGINDECLARE @py TABLE(ch char(1>,hz1 nchar(1> COLLATE Chinese_PRC_CS_AS_KS_WS,hz2 nchar(1> COLLATE Chinese_PRC_CS_AS_KS_WS>INSERT @py 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'做'DECLARE @i intSET @i=PATINDEX('%[吖-做]%' COLLATEChinese_PRC_CS_AS_KS_WS,@str>WHILE @i>0SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1>,ch> ,@i=PATINDEX('%[吖-做]%' COLLATEChinese_PRC_CS_AS_KS_WS,@str>FROM @pyWHERE SUBSTRING(@str,@i,1> BETWEEN hz1 AND hz2 RETURN(@str>ENDGO然后，使用查询语句select * from table wheresubstring(hoceedb.dbo.f_GetPY(str>,1,1> = 'A'查出table表中str字段第一个字符的拼音首字母等于‘A’的所有数据/*根据汉字获取全拼1.生成所有读音临时表2.根据Chinese_PRC_CS_AS_KS_WS 排序获取读音*/CREATE function f_GetPy(@str varchar(100>> returns varchar(8000> asbegindeclare @re varchar(8000> --生成临时表declare @t table(chr nchar(1> collate Chinese_PRC_CS_AS_KS_WS,py nvarchar(20>> insert into @t select'吖','a' insert into @t select'厑','aes' insert into @t select'哎','ai' insert into @t select'安','an' insert into @t select'肮','ang' insert into @t select'凹','ao' insert into @t select'八','ba'insert into @t select'兡','baike' insert into @t select'瓸','baiwa' insert into @t select'扳','ban' insert into @t select'邦','bang' insert into @t select'勹','bao' insert into @t select'萡','be' insert into @t select'陂','bei' insert into @t select'奔','ben' insert into @t select'伻','beng' insert into @t select'皀','bi' insert into @t select'边','bian' insert into @t select'辪','uu' insert into @t select'灬','biao' insert into @t select'憋','bie' insert into @t select'汃','bin' insert into @t select'冫','bing' insert into @t select'癶','bo' insert into @t select'峬','bu' insert into @t select'嚓','ca' insert into @t select'偲','cai' insert into @t select'乲','cal'insert into @t select'仓','cang' insert into @t select'撡','cao' insert into @t select'冊','ce' insert into @t select'膥','cen' insert into @t select'噌','ceng' insert into @t select'硛','ceok' insert into @t select'岾','ceom' insert into @t select'猠','ceon' insert into @t select'乽','ceor' insert into @t select'叉','cha' insert into @t select'犲','chai' insert into @t select'辿','chan' insert into @t select'伥','chang' insert into @t select'抄','chao' insert into @t select'车','che' insert into @t select'抻','chen' insert into @t select'阷','cheng' insert into @t select'吃','chi' insert into @t select'充','chong' insert into @t select'抽','chou' insert into @t select'出','chu'insert into @t select'巛','chuan' insert into @t select'刅','chuang' insert into @t select'吹','chui' insert into @t select'旾','chun' insert into @t select'踔','chuo' insert into @t select'呲','ci' insert into @t select'嗭','cis' insert into @t select'从','cong' insert into @t select'凑','cou' insert into @t select'粗','cu' insert into @t select'汆','cuan' insert into @t select'崔','cui' insert into @t select'邨','cun' insert into @t select'瑳','cuo' insert into @t select'撮','chua' insert into @t select'咑','da' insert into @t select'呔','dai' insert into @t select'丹','dan' insert into @t select'当','dang' insert into @t select'刀','dao' insert into @t select'恴','de'insert into @t select'扥','den' insert into @t select'灯','deng' insert into @t select'仾','di' insert into @t select'嗲','dia' insert into @t select'敁','dian' insert into @t select'刁','diao' insert into @t select'爹','die' insert into @t select'哋','dei' insert into @t select'嚸','dim' insert into @t select'丁','ding' insert into @t select'丟','diu' insert into @t select'东','dong' insert into @t select'吺','dou' insert into @t select'剢','du' insert into @t select'耑','duan' insert into @t select'叾','dug' insert into @t select'垖','dui' insert into @t select'吨','dun' insert into @t select'咄','duo' insert into @t select'妸','e' insert into @t select'奀','en'insert into @t select'仒','eo' insert into @t select'乻','eol' insert into @t select'旕','eos' insert into @t select'儿','er' insert into @t select'发','fa' insert into @t select'帆','fan' insert into @t select'匚','fang' insert into @t select'飞','fei' insert into @t select'吩','fen' insert into @t select'丰','feng' insert into @t select'瓰','fenwa' insert into @t select'覅','fiao' insert into @t select'仏','fo' insert into @t select'垺','fou' insert into @t select'夫','fu' insert into @t select'猤','fui' insert into @t select'旮','ga' insert into @t select'侅','gai' insert into @t select'甘','gan' insert into @t select'冈','gang' insert into @t select'皋','gao'insert into @t select'给','gei' insert into @t select'根','gen' insert into @t select'更','geng' insert into @t select'啹','geu' insert into @t select'喼','gib' insert into @t select'嗰','go' insert into @t select'工','gong' insert into @t select'兝','gongfen' insert into @t select'兣','gongli' insert into @t select'勾','gou' insert into @t select'估','gu' insert into @t select'瓜','gua' insert into @t select'乖','guai' insert into @t select'关','guan' insert into @t select'光','guang' insert into @t select'归','gui' insert into @t select'丨','gun' insert into @t select'呙','guo' insert into @t select'妎','ha' insert into @t select'咍','hai' insert into @t select'乤','hal'insert into @t select'魧','hang' insert into @t select'茠','hao' insert into @t select'兞','haoke' insert into @t select'诃','he' insert into @t select'黒','hei' insert into @t select'拫','hen' insert into @t select'亨','heng' insert into @t select'囍','heui' insert into @t select'乊','ho' insert into @t select'乥','hol' insert into @t select'叿','hong' insert into @t select'齁','hou' insert into @t select'乎','hu' insert into @t select'花','hua' insert into @t select'徊','huai' insert into @t select'欢','huan' insert into @t select'巟','huang' insert into @t select'灰','hui' insert into @t select'昏','hun' insert into @t select'吙','huo' insert into @t select'嚿','geo'insert into @t select'丌','ji' insert into @t select'加','jia' insert into @t select'嗧','jialun' insert into @t select'戋','jian' insert into @t select'江','jiang' insert into @t select'艽','jiao' insert into @t select'阶','jie' insert into @t select'巾','jin' insert into @t select'坕','jing' insert into @t select'冂','jiong' insert into @t select'丩','jiu' insert into @t select'欍','jou' insert into @t select'凥','ju' insert into @t select'姢','juan' insert into @t select'噘','jue' insert into @t select'军','jun' insert into @t select'咔','ka' insert into @t select'开','kai' insert into @t select'乫','kal' insert into @t select'刊','kan' insert into @t select'冚','hem'insert into @t select'尻','kao' insert into @t select'坷','ke' insert into @t select'肎','ken' insert into @t select'劥','keng' insert into @t select'巪','keo' insert into @t select'乬','keol' insert into @t select'唟','keos' insert into @t select'厼','keum' insert into @t select'怾','ki' insert into @t select'空','kong' insert into @t select'廤','kos' insert into @t select'抠','kou' insert into @t select'扝','ku' insert into @t select'夸','kua' insert into @t select'蒯','kuai' insert into @t select'宽','kuan' insert into @t select'匡','kuang' insert into @t select'亏','kui' insert into @t select'坤','kun' insert into @t select'拡','kuo' insert into @t select'穒','kweok'insert into @t select'来','lai' insert into @t select'兰','lan' insert into @t select'啷','lang' insert into @t select'捞','lao' insert into @t select'仂','le' insert into @t select'雷','lei' insert into @t select'塄','leng' insert into @t select'唎','li' insert into @t select'俩','lia' insert into @t select'嫾','lian' insert into @t select'簗','liang' insert into @t select'蹽','liao' insert into @t select'毟','lie' insert into @t select'厸','lin' insert into @t select'伶','ling' insert into @t select'溜','liu' insert into @t select'瓼','liwa' insert into @t select'囖','lo' insert into @t select'龙','long' insert into @t select'娄','lou' insert into @t select'噜','lu'insert into @t select'寽','lue' insert into @t select'孪','luan' insert into @t select'掄','lun' insert into @t select'頱','luo' insert into @t select'呣','m' insert into @t select'妈','ma' insert into @t select'遤','hweong' insert into @t select'埋','mai' insert into @t select'颟','man' insert into @t select'牤','mang' insert into @t select'匁','mangmi' insert into @t select'猫','mao' insert into @t select'唜','mas' insert into @t select'庅','me' insert into @t select'呅','mei' insert into @t select'椚','men' insert into @t select'掹','meng' insert into @t select'踎','meo' insert into @t select'瞇','mi' insert into @t select'宀','mian' insert into @t select'喵','miao'insert into @t select'瓱','miliklanm' insert into @t select'民','min' insert into @t select'冧','lem' insert into @t select'名','ming' insert into @t select'谬','miu' insert into @t select'摸','mo' insert into @t select'乮','mol' insert into @t select'哞','mou' insert into @t select'母','mu' insert into @t select'旀','myeo' insert into @t select'丆','myeon' insert into @t select'椧','myeong' insert into @t select'拏','na' insert into @t select'腉','nai' insert into @t select'囡','nan' insert into @t select'囔','nang' insert into @t select'乪','keg' insert into @t select'孬','nao' insert into @t select'疒','ne' insert into @t select'娞','nei' insert into @t select'焾','nem'insert into @t select'莻','neus' insert into @t select'鈪','ngag' insert into @t select'銰','ngai' insert into @t select'啱','ngam' insert into @t select'妮','ni' insert into @t select'年','nian' insert into @t select'娘','niang' insert into @t select'茑','niao' insert into @t select'捏','nie' insert into @t select'脌','nin' insert into @t select'宁','ning' insert into @t select'牛','niu' insert into @t select'农','nong' insert into @t select'羺','nou' insert into @t select'奴','nu' insert into @t select'女','nv' insert into @t select'疟','nue' insert into @t select'瘧','nve' insert into @t select'奻','nuan' insert into @t select'黁','nun' insert into @t select'燶','nung'insert into @t select'筽','o' insert into @t select'夞','oes' insert into @t select'乯','ol' insert into @t select'鞰','on' insert into @t select'讴','ou' insert into @t select'妑','pa' insert into @t select'俳','pai' insert into @t select'磗','pak' insert into @t select'眅','pan' insert into @t select'乓','pang' insert into @t select'抛','pao' insert into @t select'呸','pei' insert into @t select'瓫','pen' insert into @t select'匉','peng' insert into @t select'浌','peol' insert into @t select'巼','phas' insert into @t select'闏','phdeng' insert into @t select'乶','phoi' insert into @t select'喸','phos' insert into @t select'丕','pi' insert into @t select'囨','pian'insert into @t select'氕','pie' insert into @t select'丿','pianpang' insert into @t select'姘','pin' insert into @t select'乒','ping' insert into @t select'钋','po' insert into @t select'剖','pou' insert into @t select'哣','deo' insert into @t select'兺','ppun' insert into @t select'仆','pu' insert into @t select'七','qi' insert into @t select'掐','qia' insert into @t select'千','qian' insert into @t select'羌','qiang' insert into @t select'兛','qianke' insert into @t select'瓩','qianwa' insert into @t select'悄','qiao' insert into @t select'苆','qie' insert into @t select'亲','qin' insert into @t select'蠄','kem' insert into @t select'氢','qing' insert into @t select'銎','qiong'insert into @t select'曲','qu' insert into @t select'迲','keop' insert into @t select'峑','quan' insert into @t select'蒛','que' insert into @t select'夋','qun' insert into @t select'亽','ra' insert into @t select'囕','ram' insert into @t select'呥','ran' insert into @t select'穣','rang' insert into @t select'荛','rao' insert into @t select'惹','re' insert into @t select'人','ren' insert into @t select'扔','reng' insert into @t select'日','ri' insert into @t select'栄','rong' insert into @t select'禸','rou' insert into @t select'嶿','ru' insert into @t select'撋','ruan' insert into @t select'桵','rui' insert into @t select'闰','run' insert into @t select'叒','ruo'insert into @t select'栍','saeng' insert into @t select'毢','sai' insert into @t select'虄','sal' insert into @t select'三','san' insert into @t select'桒','sang' insert into @t select'掻','sao' insert into @t select'色','se' insert into @t select'裇','sed' insert into @t select'聓','sei' insert into @t select'森','sen' insert into @t select'鬙','seng' insert into @t select'閪','seo' insert into @t select'縇','seon' insert into @t select'杀','sha' insert into @t select'筛','shai' insert into @t select'山','shan' insert into @t select'伤','shang' insert into @t select'弰','shao' insert into @t select'奢','she' insert into @t select'申','shen' insert into @t select'升','sheng'insert into @t select'兙','shike' insert into @t select'瓧','shiwa' insert into @t select'収','shou' insert into @t select'书','shu' insert into @t select'刷','shua' insert into @t select'摔','shuai' insert into @t select'闩','shuan' insert into @t select'双','shuang' insert into @t select'谁','shei' insert into @t select'脽','shui' insert into @t select'吮','shun' insert into @t select'哾','shuo' insert into @t select'丝','si' insert into @t select'螦','so' insert into @t select'乺','sol' insert into @t select'忪','song' insert into @t select'凁','sou' insert into @t select'苏','su' insert into @t select'痠','suan' insert into @t select'夊','sui' insert into @t select'孙','sun'insert into @t select'他','ta' insert into @t select'襨','tae' insert into @t select'囼','tai' insert into @t select'坍','tan' insert into @t select'铴','tang' insert into @t select'仐','tao' insert into @t select'畓','tap' insert into @t select'忒','te' insert into @t select'膯','teng' insert into @t select'唞','teo' insert into @t select'朰','teul' insert into @t select'剔','ti' insert into @t select'天','tian' insert into @t select'旫','tiao' insert into @t select'怗','tie' insert into @t select'厅','ting' insert into @t select'乭','tol' insert into @t select'囲','tong' insert into @t select'偷','tou' insert into @t select'凸','tu' insert into @t select'湍','tuan'insert into @t select'旽','tun' insert into @t select'乇','tuo' insert into @t select'屲','wa' insert into @t select'歪','wai' insert into @t select'乛','wan' insert into @t select'尣','wang' insert into @t select'危','wei' insert into @t select'塭','wen' insert into @t select'翁','weng' insert into @t select'挝','wo' insert into @t select'乌','wu' insert into @t select'夕','xi' insert into @t select'诶','ei' insert into @t select'疨','xia' insert into @t select'仙','xian' insert into @t select'乡','xiang' insert into @t select'灱','xiao' insert into @t select'楔','xie' insert into @t select'心','xin' insert into @t select'星','xing' insert into @t select'凶','xiong'insert into @t select'旴','xu' insert into @t select'昍','xuan' insert into @t select'疶','xue' insert into @t select'坃','xun' insert into @t select'丫','ya' insert into @t select'咽','yan' insert into @t select'欕','eom' insert into @t select'央','yang' insert into @t select'吆','yao' insert into @t select'椰','ye' insert into @t select'膶','yen' insert into @t select'一','yi' insert into @t select'乁','i' insert into @t select'乚','yin' insert into @t select'应','ying' insert into @t select'哟','yo' insert into @t select'佣','yong' insert into @t select'优','you' insert into @t select'迂','yu' insert into @t select'囦','yuan' insert into @t select'曰','yue'insert into @t select'帀','za' insert into @t select'災','zai' insert into @t select'兂','zan' insert into @t select'牂','zang' insert into @t select'遭','zao' insert into @t select'啫','ze' insert into @t select'贼','zei' insert into @t select'怎','zen' insert into @t select'曽','zeng' insert into @t select'吒','zha' insert into @t select'甴','gad' insert into @t select'夈','zhai' insert into @t select'毡','zhan' insert into @t select'张','zhang' insert into @t select'钊','zhao' insert into @t select'蜇','zhe' insert into @t select'贞','zhen' insert into @t select'凧','zheng' insert into @t select'之','zhi' insert into @t select'中','zhong' insert into @t select'州','zhou'insert into @t select'抓','zhua' insert into @t select'专','zhuan' insert into @t select'转','zhuai' insert into @t select'妆','zhuang' insert into @t select'骓','zhui' insert into @t select'宒','zhun' insert into @t select'卓','zhuo' insert into @t select'孜','zi' insert into @t select'唨','zo' insert into @t select'宗','zong' insert into @t select'棸','zou' insert into @t select'哫','zu' insert into @t select'劗','zuan' insert into @t select'厜','zui' insert into @t select'尊','zun' insert into @t select'昨','zuo'declare @strlen int select @strlen=len(@str>,@re='' while @strlen>0beginselect top 1 p1EanqFDPw@re=UPPER(substring(py,1,1> >+substring(py,2,len(py>>+@re,@ strlen=DXDiTa9E3d@strlen-1from @t a where chr<=substring(@str,@strlen,1>order by chr collate Chinese_PRC_CS_AS_KS_WS descif @@rowcount=0select @re=substring(@str,@strlen,1>+@re,@strlen=@strlen-1end return(@re>endRTCrpUDGiT通过SQL Server取出汉字的拼音用排序规则特点计算汉字笔划和取得拼音首字母SQL SERVER的排序规则平时使用不是很多，也许不少初学者还比较陌生，但有一个错误大家应是经常碰到: SQL SERVER数据库，在跨库多表连接查询时，若两数据库默认字符集不同，系统就会返回这样的错误： "无法解决 equal to 操作的排序规则冲突。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
go
--创建取汉字首字母函数(第一版)
create function [dbo].[f_getpy_V1] (@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 199 68+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
go
--创建取汉字首字母函数(第二版)
create function [dbo].[f_getpy_V2](@Str varchar(500)='') returns varchar(500)
as
begin
declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)
select @strlen=len(@str),@return='',@ii=0
set @ii=0
while @ii<@strlen
begin
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
if @chn>'z'
select @n = @n +1
,@c =case chn when @chn then char(@n)else @c end
from(
select top 27 *from(
select chn ='吖'
union all select'八'
union all select'嚓'
union all select'咑'
union all select'妸'
union all select'发'
union all select'旮'
union all select'铪'
union all select'丌'--because have no 'i'
union all select'丌'
union all select'咔'
union all select'垃'
union all select'嘸'
union all select'拏'
union all select'噢'
union all select'妑'
union all select'七'
union all select'呥'
union all select'仨'
union all select'他'
union all select'屲'--no 'u'
union all select'屲'--no 'v'
union all select'屲'
union all select'夕'
union all select'丫'
union all select'帀'
union all select @chn)as a
order by chn COLLATE Chinese_PRC_CI_AS
)as b
else set @c='a'
set @return=@return+@c
end
return(@return)
end
--测试示例
select dbo.[f_getpy_V1]('王立国')as V11
select dbo.[f_getpy_V2]('王立国')as V21
select dbo.[f_getpy_V1]('重复')as V21
select dbo.[f_getpy_V2]('重复')as V22
--运行结果
/*
V11
--------
WLG
V21
--------
WLG
V21
--------
ZF
V22
--------
ZF
*/
--由上我们可以看到，两种方法都没有解决多音字的问题。