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 操作的排序规则冲突。
SQL如何让中文名字按名字第一个字的拼音首字母排序
博客园 用户登录 代码改变世界 密码登录 短信登录 忘记登录用户名 忘记密码 记住我 登何 让 中 文 名 字 按 名 字 第 一 个 字 的 拼 音 首 字 母 排 序
select isnull(b.py,upper(left(ername,1))) as py, ername from ( select 'a3' as username union select '中华人民共和国' union select '周笔畅' union select '郭安俊' union select '天堂' union select '包振' union select '魔兽' union select '精灵' union select '安全第一' ) a left outer join ( select 'A' as PY,N'驁' as word, N'啊' as sword union select 'B',N'簿',N'驁' union select 'C',N'錯',N'簿' union select 'D',N'鵽',N'錯' union select 'E',N'樲',N'鵽' union select 'F',N'鰒',N'樲' union select 'G',N'腂',N'鰒' union select 'H',N'夻',N'腂' union select 'J',N'攈',N'夻' union select 'K',N'穒',N'攈' union select 'L',N'鱳',N'穒' union select 'M',N'旀',N'鱳' union select 'N',N'桛',N'旀' union select 'O',N'漚',N'桛' union select 'P',N'曝',N'漚' union select 'Q',N'囕',N'曝' union select 'R',N'鶸',N'囕' union select 'S',N'蜶',N'鶸' union select 'T',N'籜',N'蜶' union select 'W',N'鶩',N'籜' union select 'X',N'鑂',N'鶩' union select 'Y',N'韻',N'鑂' union select 'Z',N'咗',N'韻' ) b on left(username,1) between b.sword and b.word
sqlserver2008实现拼音首字母和随机n位数的生成-电脑资料
sqlserver2008实现拼音首字母和随机n位数的生成-电脑资料应用背景：1、一些商品需要使用汉字拼音首字母快速查找，以便定位和选择数据；2、一些系统的用户名可能含有该用户姓名首字母，以方便记忆，我做系统的时候，我习惯把用户名定义为：用户姓名汉字首字母+用户在表中的主键ID，这样不会出现用户名重复，也方便用户记忆，。
3、初始化用户密码的时候，有时需要自动生成n位随即密码。
说明：技术没有原创，只有在应用中不断吸取他人经验，不断总结。
一、获取拼音首字母的编码和引用1、在sqlserver2008中创建标量函数，函数名CreateFirstLetter。
创建方法：您的数据库-》可编程性=》函数=》标量值函数；右键，创建标量值函数CreateFirstLetter。
函数体代码如下：Create function [dbo].[CreateFirstLetter](@col varchar(2000))returns varchar(2000)begindeclare @cyc int,@len int,@sql varchar(1000),@char varbinary(20)select @cyc = 1,@len = len(@col),@sql = ''while @cyc <= @lenbeginselect @char = cast(substring(@col, @cyc, 1) as varbinary)if @char>=0XB0A1 and @char<=0XB0C4 setelse if @char>=0XB0C5 and @char<=0XB2C0 set @sql=@sql+'B'else if @char>=0XB2C1 and @char<=0XB4ED set @sql=@sql+'C'else if @char>=0XB4EE and @char<=0XB6E9 set @sql=@sql+'D'else if @char>=0XB6EA and @char<=0XB7A1 set @sql=@sql+'E'else if @char>=0XB7A2 and @char<=0XB8C0 set @sql=@sql+'F'else if @char>=0XB8C1 and @char<=0XB9FD set @sql=@sql+'G'else if @char>=0XB9FE and @char<=0XBBF6 set @sql=@sql+'H'else if @char>=0XBBF7 and @char<=0XBFA5 set @sql=@sql+'J'else if @char>=0XBFA6 and @char<=0XC0AB set @sql=@sql+'K'else if @char>=0XC0AC and @char<=0XC2E7 set @sql=@sql+'L'else if @char>=0XC2E8 and @char<=0XC4C2 set @sql=@sql+'M'else if @char>=0XC4C3 and @char<=0XC5B5 set @sql=@sql+'N'else if @char>=0XC5B6 and @char<=0XC5BD set @sql=@sql+'O'else if @char>=0XC5BE and @char<=0XC6D9 set @sql=@sql+'P'else if @char>=0XC6DA and @char<=0XC8BA setelse if @char>=0XC8BB and @char<=0XC8F5 set @sql=@sql+'R'else if @char>=0XC8F6 and @char<=0XCBF9 set @sql=@sql+'S'else if @char>=0XCBFA and @char<=0XCDD9 set @sql=@sql+'T'else if @char>=0XCDDA and @char<=0XCEF3 set @sql=@sql+'W'else if @char>=0XCEF4 and @char<=0XD1B8 set @sql=@sql+'X'else if @char>=0XD1B9 and @char<=0XD4D0 set @sql=@sql+'Y'else if @char>=0XD4D1 and @char<=0XD7F9 set @sql=@sql+'Z'set @cyc = @cyc + 1endreturn @sqlend2、函数使用在你的存储过程或者查询窗体中引用该函数即可，电脑资料《sql server 2008实现拼音首字母和随机n位数的生成》(https://)。
通过SQL Server取出汉字的拼音
通过SQL Server取出汉字的拼音用排序规则特点计算汉字笔划和取得拼音首字母SQL SERVER的排序规则平时使用不是很多，也许不少初学者还比较陌生，但有一个错误大家应是经常碰到: SQL SERVER数据库，在跨库多表连接查询时，若两数据库默认字符集不同，系统就会返回这样的错误："无法解决 equal to 操作的排序规则冲突。
"一.错误分析：这个错误是因为排序规则不一致造成的，我们做个测试，比如：create table #t1(name varchar(20) collate Albanian_CI_AI_WS,value int)create table #t2(name varchar(20) collate Chinese_PRC_CI_AI_WS,value int )表建好后，执行连接查询：select * from #t1 A inner join #t2 B on =这样，错误就出现了：服务器: 消息 446，级别 16，状态 9，行 1无法解决 equal to 操作的排序规则冲突。
要排除这个错误，最简单方法是，表连接时指定它的排序规则，这样错误就不再出现了。
语句这样写：select *from #t1 A inner join #t2 Bon = collate Chinese_PRC_CI_AI_WS二.排序规则简介：什么叫排序规则呢？MS是这样描述的："在 Microsoft SQL Server 2000 中，字符串的物理存储由排序规则控制。
排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则。
"在查询分析器内执行下面语句，可以得到SQL SERVER支持的所有排序规则。
select * from ::fn_helpcollations()排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。
如：Chinese_PRC_CS_AI_WS前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则。
通过sqlserver取出汉字的拼音
通过SQL Server取出汉字的拼音用排序规则特点计算汉字笔划和取得拼音首字母SQL SERVER的排序规则平时使用不是很多，也许不少初学者还比较陌生，但有一个错误大家应是经常碰到: SQL SERVER数据库，在跨库多表连接查询时，若两数据库默认字符集不同，系统就会返回这样的错误："无法解决 equal to 操作的排序规则冲突。
"一.错误分析：这个错误是因为排序规则不一致造成的，我们做个测试，比如：create table #t1(name varchar(20) collate Albanian_CI_AI_WS,value int)create table #t2(name varchar(20) collate Chinese_PRC_CI_AI_WS,value int )表建好后，执行连接查询：select * from #t1 A inner join #t2 B on =这样，错误就出现了：服务器: 消息 446，级别 16，状态 9，行 1无法解决 equal to 操作的排序规则冲突。
要排除这个错误，最简单方法是，表连接时指定它的排序规则，这样错误就不再出现了。
语句这样写：select *from #t1 A inner join #t2 Bon = collate Chinese_PRC_CI_AI_WS二.排序规则简介：什么叫排序规则呢？MS是这样描述的："在 Microsoft SQL Server 2000 中，字符串的物理存储由排序规则控制。
排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则。
"在查询分析器内执行下面语句，可以得到SQL SERVER支持的所有排序规则。
select * from ::fn_helpcollations()排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。
如：Chinese_PRC_CS_AI_WS前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则。
提取字段的函数
提取字段的函数在数据分析和处理中，经常需要从文本或表格中提取特定的字段，例如从一组名字中提取出姓氏、名字或全名，或者从一段文字中提取出关键词或信息。
为此，我们可以使用一些专门提取字段的函数来实现。
不同的编程语言和软件工具提供了不同的函数，以下是一些常用的函数：1. SUBSTRING函数：用于提取文本字符串的一部分。
该函数需要指定文本字符串、起始位置和要提取的长度。
例如，在SQL中可以使用以下语句从一个名字字段中提取姓氏：```SELECT SUBSTRING(name, 1, 3) as surname FROM table;```这将提取name字段的前三个字符作为姓氏。
2. REGEX函数：用于根据正则表达式提取文本中的特定模式。
该函数可以在多种编程语言和软件中使用，例如Python、R和JavaScript。
例如，在Python中可以使用re模块的findall函数从一段文字中提取所有包含数字的单词：```import retext = 'There are 123 apples and 456 oranges.'result = re.findall(r'd+', text)print(result)```这将输出['123', '456']，即所有包含数字的单词。
3. SPLIT函数：用于根据分隔符将文本字符串分割为多个部分。
该函数需要指定分隔符，并将返回一个包含分割后的部分的数组。
例如，在JavaScript中可以使用以下语句从一个逗号分隔的字符串中提取不同的字段：```var str = 'John,Doe,35';var parts = str.split(',');var surname = parts[0];var firstname = parts[1];var age = parts[2];```这将分割字符串为三部分，并将它们赋值给不同的变量。
sql server提取字段结果中的汉字 语句
sql server提取字段结果中的汉字语句
要从SQL Server中提取字段结果中的汉字，你可以使用正则表达式函数`PATINDEX`和`SUBSTRING`结合来实现。
以下是一个示例SQL语句，演示如何提取字段结果中的汉字：
```sql
DECLARE input VARCHAR(255) = 'Hello World';
DECLARE pattern VARCHAR(255) = '%[一-龥]+%'; -- 匹配汉字的正则表达式
IF PATINDEX(pattern, input) > 0
BEGIN
SELECT SUBSTRING(input, PATINDEX(pattern, input), LEN(input) - PATINDEX(pattern, input) + 1) AS ExtractedChinese;
END
ELSE
BEGIN
SELECT NULL; -- 没有匹配到汉字
END
```
在这个示例中，我们首先声明了一个输入字符串`input`，然后定义了一个正则表达式模式`pattern`，用于匹配汉字。
接下来，我们使用`PATINDEX`函数来查找匹配的起始位置，如果存在匹配，则使用`SUBSTRING`函数提取出匹配的汉字部分。
如果没有匹配到汉字，则返回NULL。
请注意，上述示例中的正则表达式模式`pattern`用于匹配任何位置的汉字。
如果你想匹配特定位置的汉字，可以根据需要进行修改。
另外，如果你需要提取多个连续的汉字，可以使用循环和递归来实现。
SQL取得汉字拼音首字母（转载）
SQL取得汉字拼⾳⾸字母（转载）create function GetPY(@str varchar(500))returns varchar(500)asbegindeclare@cyc int,@length int,@str1varchar(100),@charcate varbinary(20)set@cyc=1--从第⼏个字开始取set@length=len(@str)--输⼊汉字的长度set@str1=''--⽤于存放返回值while@cyc<=@lengthbeginselect@charcate=cast(substring(@str,@cyc,1) as varbinary)--每次取出⼀个字并将其转变成⼆进制,便于与GBK编码表进⾏⽐较if@charcate>=0XB0A1and@charcate<=0XB0C4set@str1=@str1+'A'--说明此汉字的⾸字母为A,以下同上else if@charcate>=0XB0C5and@charcate<=0XB2C0set@str1=@str1+'B'else if@charcate>=0XB2C1and@charcate<=0XB4EDset@str1=@str1+'C'else if@charcate>=0XB4EE and@charcate<=0XB6E9set@str1=@str1+'D'else if@charcate>=0XB6EA and@charcate<=0XB7A1set@str1=@str1+'E'else if@charcate>=0XB7A2and@charcate<=0XB8C0set@str1=@str1+'F'else if@charcate>=0XB8C1and@charcate<=0XB9FDset@str1=@str1+'G'else if@charcate>=0XB9FE and@charcate<=0XBBF6set@str1=@str1+'H'else if@charcate>=0XBBF7and@charcate<=0XBFA5set@str1=@str1+'J'else if@charcate>=0XBFA6and@charcate<=0XC0ABset@str1=@str1+'K'else if@charcate>=0XC0AC and@charcate<=0XC2E7set@str1=@str1+'L'else if@charcate>=0XC2E8and@charcate<=0XC4C2set@str1=@str1+'M'else if@charcate>=0XC4C3and@charcate<=0XC5B5set@str1=@str1+'N'else if@charcate>=0XC5B6and@charcate<=0XC5BDset@str1=@str1+'O'else if@charcate>=0XC5BE and@charcate<=0XC6D9set@str1=@str1+'P'else if@charcate>=0XC6DA and@charcate<=0XC8BAset@str1=@str1+'Q'else if@charcate>=0XC8BB and@charcate<=0XC8F5set@str1=@str1+'R'else if@charcate>=0XC8F6and@charcate<=0XCBF9set@str1=@str1+'S'else if@charcate>=0XCBFA and@charcate<=0XCDD9set@str1=@str1+'T'else if@charcate>=0XCDDA and@charcate<=0XCEF3set@str1=@str1+'W'else if@charcate>=0XCEF4and@charcate<=0XD1B8set@str1=@str1+'X'else if@charcate>=0XD1B9and@charcate<=0XD4D0set@str1=@str1+'Y'else if@charcate>=0XD4D1and@charcate<=0XD7F9set@str1=@str1+'Z'set@cyc=@cyc+1--取出输⼊汉字的下⼀个字endreturn@str1--返回输⼊汉字的⾸字母end--测试数据select dbo.GetPY('中华⼈民共和国')。
mysql 汉字首字母 函数
mysql 汉字首字母函数如果你想要从MySQL数据库中的汉字字段提取每个汉字的首字母，你可以使用以下的方法：1. 使用自定义函数:你可以创建一个自定义的MySQL函数来处理这个任务。
以下是一个简单的示例，它使用`SUBSTRING`和`CONVERT`函数来提取每个汉字的首字母：```sqlDELIMITER //CREATE FUNCTION get_chinese_initials(input_str VARCHAR(255)) RETURNS VARCHAR(255)BEGINDECLARE output_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 1;DECLARE len INT;DECLARE temp_str VARCHAR(2);SET len = LENGTH(input_str);WHILE i <= len DOSET temp_str = SUBSTRING(input_str, i, 1);SET output_str = CONCAT(output_str, SUBSTRING(UPPER(CONVERT(temp_str USING utf8)), 1, 1)); SET i = i + 1;END WHILE;RETURN output_str;END //DELIMITER ;```使用方法：```sqlSELECT get_chinese_initials('你好世界'); -- 输出: 'NH'```2. 使用现有工具或库:有一些现有的工具或库可以帮助你完成这个任务，例如使用Python的`py 汉子拼音`库，然后从数据库中提取数据，并在应用程序级别处理它。
这可能需要一些编程知识和集成，但它提供了更多的灵活性。
3. 注意:汉字到拼音的转换是一个复杂的过程，因为有很多同音字和多音字。
oracle sql 字符函数
oracle sql 字符函数Oracle SQL提供了许多有用的字符函数，用于处理和操作字符串数据。
这些函数可以用于从字符串中提取子字符串、转换字符串的大小写、连接字符串等操作。
下面我将介绍一些常用的Oracle SQL字符函数：1. SUBSTR函数，SUBSTR函数用于从字符串中提取子字符串。
它的语法为SUBSTR(string, start_position, length)，其中string是要提取子字符串的原始字符串，start_position是子字符串的起始位置，length是要提取的子字符串的长度。
2. INSTR函数，INSTR函数用于查找字符串中某个子字符串的位置。
它的语法为INSTR(string, substring)，其中string是要进行查找的原始字符串，substring是要查找的子字符串。
该函数还可以指定起始位置和搜索方向等参数。
3. CONCAT函数，CONCAT函数用于连接两个字符串。
它的语法为CONCAT(string1, string2)，其中string1和string2是要进行连接的两个字符串。
4. UPPER和LOWER函数，UPPER函数用于将字符串转换为大写，LOWER函数用于将字符串转换为小写。
5. TRIM函数，TRIM函数用于去除字符串开头或结尾的空格或指定的字符。
它的语法为TRIM([LEADING | TRAILING | BOTH] [trim_character] FROM string)，其中trim_character是要去除的字符，可以省略，默认情况下去除空格。
6. REPLACE函数，REPLACE函数用于替换字符串中的指定子字符串。
它的语法为REPLACE(string, old_substring,new_substring)，其中string是要进行替换的原始字符串，old_substring是要被替换的子字符串，new_substring是替换后的新子字符串。
sql常用函数instr()和substr()
在Oracle中可以使用instr函数对某个字符串进行判断，判断其是否含有指定的字符。
其语法为：instr(sourceString,destString,start,appearPosition). instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）其中sourceString代表源字符串；destString代表想聪源字符串中查找的子串；start代表查找的开始位置，该参数可选的，默认为1；appearPosition代表想从源字符中查找出第几次出现的destString，该参数也是可选的，默认为1；如果start的值为负数，那么代表从右往左进行查找，但是位置数据仍然从左向右计算。
返回值为：查找到的字符串的位置。
对于instr函数，我们经常这样使用：从一个字符串中查找指定子串的位置。
例如：SQL> select instr('yuechaotianyuechao','ao') position from dual;POSITION----------6从第7个字符开始搜索SQL> select instr('yuechaotianyuechao','ao', 7) position from dual;POSITION----------17从第1个字符开始，搜索第2次出现子串的位置SQL> select instr('yuechaotianyuechao','ao', 1, 2) position from dual;POSITION----------17注意：1。
若‘起始位置’=0 时返回结果为0，2。
这里只有三个参数，意思是查找第一个要查找字符的位置(因为‘第几次出现’默认为1),当‘起始位置’不大于要查找的第一个字符的位置时，返回的值都将是第一个字符的位置，如果‘起始位置’大于要查找的第一个字符的位置时，返回的值都将是第2个字符的位置,依此类推……（但是也是以第一个字符开始计数）substr函数的用法，取得字符串中指定起始位置和长度的字符串，默认是从起始位置到结束的子串。
sql substring 中文
sql substring 中文
SQL中的SUBSTRING函数用于从字符串中提取子字符串。
对于
包含中文的字符串，我们可以使用SUBSTRING函数来提取中文字符。
在SQL中，可以使用SUBSTRING函数来提取中文字符，但需要
注意中文字符在字符串中所占的字节数。
由于中文字符通常占据多
个字节，所以在使用SUBSTRING函数时需要考虑字符的字节长度而
不是字符数。
假设我们有一个包含中文的字符串"你好，世界"，如果我们想
要提取其中的"你好"，可以使用如下的SQL语句：
sql.
SELECT SUBSTRING('你好，世界', 1, 2)。
这条SQL语句将返回字符串的前两个字符，即"你好"。
在这里，我们需要注意到"你好"这两个中文字符共占据了4个字节的空间。
另外，还可以使用UNICODE和NCHAR函数来处理中文字符。
UNICODE函数可以返回指定字符的Unicode编码，而NCHAR函数可以将Unicode编码转换为对应的字符。
这在处理中文字符时也是非常有用的。
总之，在SQL中处理包含中文的字符串时，可以使用SUBSTRING函数结合字符的字节长度来提取需要的子字符串，也可以借助UNICODE和NCHAR函数来进行更加灵活和精确的处理。
希望这些信息能够帮助到你。
- 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
*/
--由上我们可以看到，两种方法都没有解决多音字的问题。