SQLServe提供了简单的字符模糊匹配功能，比如：like，patindex，但是对于一些字符处理场景还不够，日常生活中遇到的几个问题有分别是：同一个字符/字符串，同一个字符出现了多少次，第N次出现同一个字符的位置是否连续，合并后的字符是否为有效的IP/身份证号/手机号等。同一个字符/String，同一个字符出现多少次，换成空字符串，可以计算declare@textvarchar(1000)declare@strvarchar(10)set@text='ABCBDBE'set@str='B'selectlen(@text)-len(replace(@text,@str,''))是同一个字符串，还是替换，因为有多个字符，方法一替换后需要做除法；方法2在替换时增加一个字符不需要——方法1declare@textvarchar(1000)declare@strvarchar(10)set@text='ABBBBCBBBBBBBE'set@str='BBB'select(len(@text)-len(replace(@text,@str,'')))/len(@str)—方法2declare@textvarchar(1000)declare@strvarchar(10)set@text='ABBBBCBBBBBBBE'set@str='BBB'selectlen(replace(@text,@str,@str+'_'))-len(@text)2.对于同一个字符/字符串，第N个出现位置SQLSERVER使用CHARINDEX定位字符位置：CHARINDEX(expressionToFind,expressionToSearch[,start_location])CHARINDEX(expressionToFind,expressionToSearch[,start_location])可以从指定位置开始查找，但是不能取第N个出现位置，需要自己写SQL来补充，有以下思路：1.自定义函数,循环中每次给charindex加一个计数器计数直到Nifobject_id('NthChar','FN')不为空dropfunctionNthcharGOcreatefunctionNthChar(@source_stringasnvarchar(4000),@sub_stringasnvarchar(1024),@nthasint)返回intasbegindeclare@postionintdeclare@countintset@postion=CHARINDEX(@sub_string,@source_string)设置@count=0while@postion>0beginset@count=@count+1if@count=@nthbeginbreakendset@postion=CHARINDEX(@sub_string,@source_string,@postion+1)结束返回@postionendGO--selectdbo.NthChar('abcabc','abc',2)--42。通过CTE，对整个待处理的表字段进行操作，递归每次在charindex中添加一个计数，直到Nifobject_id('tempdb..#T')不为nulldroptable#Tcreatetable#T(source_stringnvarchar(4000))insertinto#Tvalues(N'usus')insertinto#Tvalues(N'mymeohme')declare@sub_stringnvarchar(1024)declare@nthintset@sub_string=N'we'set@nth=2;withT(source_string,starts,pos,nth)as(selectsource_string,1,charindex(@sub_string,source_string),1from#tunionall选择source_string,pos+1,charindex(@sub_string,source_string,pos+1),nth+1fromTwherepos>0)selectsource_string,pos,nthfromTwherepos<>0andnth=@ntorderbysource_string,starts--source_stringposnth--ourus323.要借助一个tally表在不同的起始位置做charindex，需要先自己构造一个数表--numbers/tallytableIFEXISTS(select*从dbo.sysobjectswhereid=object_id(N'[dbo].[Numbers]')andOBJECTPROPERTY(id,N'IsUserTable')=1)DROPTABLEdbo.Numbers--=====创建并填充Tally即时表SELECTTOP1000000IDENTITY(int,1,1)ASnumberINTOdbo.NumbersFROMmaster.dbo.syscolumnssc1,master.dbo.syscolumnssc2--=====添加主键以最大化性能ALTERTABLEdbo.NumbersADDCONSTRAINTPK_numbers_numberPRIMARYKEYCLUSTERED(number)--=====允许普通大众使用GRANTSELECTONdbo.NumbersTOPUBLIC--上面的数表可以创建一次，做es不需要每次都重复createDECLARE@source_stringnvarchar(4000),@sub_stringnvarchar(1024),@nthintSET@source_string='abcabcvvvvabc'SET@sub_string='abc'SET@nth=2;WITHTAS(SELECTROW_NUMBER()OVER(ORDERBYnumber)ASnth,numberAS[位置在String]FROMdbo.NumbersnWHEREn.number<=LEN(@source_string)ANDCHARINDEX(@sub_string,@source_string,n.number)-number=0----OR--ANDSUBSTRING(@source_string,number,LEN(@sub_string))=@sub_string)SELECT*FROMTWHEREnth=@nth4.CROSSAPPLY和charindex结合适用于N值较小的时候，因为CROSSAPPLY的次数会随着N的增加而增加，语句也要相应修改declare@Ttable(source_stringnvarchar(4000))insertinto@Tvalues('abcabc'),('abcabcvvvvabc')declare@sub_stringnvarchar(1024)set@sub_string='abc'selectsource_string,p1.posasno1,p2.posasno2,p3.posasno3来自@Tcross应用（选择（charindex（@sub_string，source_string）））作为P1（Pos）交叉应用（选择（charindex（@sub_string，source_string，P1.Pos+1）））作为P2（Pos）交叉应用（选择（charindex（@sub_string,source_string,P2.Pos+1)))作为P3(Pos)5。SSIS中有内置函数，但T-SQL中没有--SQLServer2005中的FINDSTRINGSSISFINDSTRING([yourColumn],"|",2),--SQLServer2012中的TOKENSSISTOKEN(Col1,"|",3）注意：不难发现，这些方法和字符串拆分的逻辑类似，只不过一个是定位，一个是拦截。获取第N个字符前后的一个/多个字符，以N的位置，合并子串截取；3、多个相同的字符是连续的，最常见的合并为一个字符的方式是将多个连续的空格合并为一个空格，有两种解决方法：1.使用多个replace比较容易想到，其实不然确定要替换多少次，所以要循环多次——用一个空格替换两个连续的空格，然后循环直到charindex检查两个连续的空格declare@strvarchar(100)set@str='abcabckljlkkljkl'while(charindex('',@str)>0)beginselect@str=replace(@str,'','')endselect@str2,按照空格分割字符串，修剪或替换每段拆分字符串，然后用空格连接，有点繁琐，没有代码示例，如何拆分字符串可以参考：“第N次出现的位置”；4、是否是有效的IP/身份证号/手机号等IP/身份证号/手机号等这些字符串往往有自己特定的规则。可以通过子串逐位或逐段判断，但SQL语句的性能往往较差。建议尝试常规功能，见下文五。正则表达式功能1、从Oracle10g开始，可以在查询中使用正则表达式。它是通过一些支持正则表达式的函数实现的：Oracle10gREGEXP_LIKEREGEXP_REPLACEREGEXP_INSTRREGEXP_SUBSTROracle11g(new)REGEXP_COUNTOracle使用REGEXP函数处理上面的问题：（1）相同的字符/字符串出现了多少次selectlength(regexp_replace('123-345-566','[^-]',''))fromdual;selectREGEXP_COUNT('123-345-566','-')fromdual;--Oracle11g(2)对于同一个字符/字符串，第N次出现的位置不需要正则化，ORACLE的instr可以直接查找位置：instr('source_string','sub_string'[,n][,m])n表示从第n个字符开始查找，默认值为1，m表示第m次出现，默认值为1。selectinstr('abcdefghijkabc','abc',1,2)positionfromdual;(3)多个相同的字符合并为一个字符selectregexp_replace(trim('agcff'),'\s+','')fromdual;(4)是否为有效IP/身份证号/手机号等——是否有效IPWITHIPAS(SELECT'10.20.30.40'ip_addressFROMdualUNIONALLSELECT'a.b.c.d'ip_addressFROMdualUNIONALLSELECT'256.123.0.254'ip_addressFROMdualUNIONALLSELECT'255.255.255.255'ip_addressFROMdual)SELECT*FROMIPWHEREREGEXP_LIKE(ip_address,'^(([0-9]{1}|[0-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])\.){3}([0-9]{1}|[0-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])$');是否为有效身份证/手机号，暂不举例。2、SQLServer最新版本为SQLServer2017，暂不支持REGEXP功能。需要通用CLR对其进行扩展。下面是CLR的REG_REPLACE的实现：1.打开CLREXECsp_configure'showadvancedoptions','1'GORECONFIGUREGOEXECsp_configure'clrenabled','1'GORECONFIGUREGOEXECsp_configure'showadvancedoptions','0';GO2，创建Assembly3，创建CLR函数CREATEFUNCTION[dbo].[regex_replace](@input[nvarchar](4000),@pattern[nvarchar](4000),@replacement[nvarchar](4000))RETURNS[nvarchar](4000)作为调用者执行，在NULL输入外部名称[RegexUtility].[RegexUtility].[RegexReplaceDefault]GO4上返回NULL。使用regex_replace替换多个空格选择dbo.regex_replace('agcff','\s+','')替换一个空格；注意：更多的REGEXP函数可以通过CLR实现。如果你有高级的语言开发能力，你可以自己开发；或者直接用一些开源贡献的就OK。总结：1.非常规SQL语句的思想往往适用于不同的数据库；2.正则表达式中的规则（模式）在不同的开发语言中，有很多语法是相通的，通常遵循perl或linuxshellsed等工具；3、从性能上看，一般SQL判断>REGEXP函数>自定义SQL函数。总结：以上就是小编为大家介绍的SqlServer类似正则表达式的字符处理问题。希望对您有所帮助。
