正则表达式任意字符 14 类似正则表达式的字符处理问题(1/2)

SQL Serve提供了简单的字符模糊匹配功能，比如：like, patindex，不过对于某些字符处理场景还显得并不足够，日常碰到的几个问题有：

• 1. 同一个字符/字符串，出现了多少次
• 2. 同一个字符，第N次出现的位置
• 3. 多个相同字符连续，合并为一个字符
• 4. 是否为有效IP/身份证号/手机号等

一. 同一个字符/字符串，出现了多少次

同一个字符，将其替换为空串，即可计算

declare@textvarchar(1000)
declare@strvarchar(10)
set@text='ABCBDBE'set@str='B'selectlen(@text) -len(replace(@text,@str,''))

同一个字符串，仍然是替换，因为是多个字符，方法1替换后需要做一次除法；方法2替换时增加一个字符，则不需要

--方法1declare@textvarchar(1000)
declare@strvarchar(10)
set@text='ABBBCBBBDBBBE'set@str='BBB'select (len(@text) -len(replace(@text,@str,'')))/len(@str)

--方法2declare@textvarchar(1000)
declare@strvarchar(10)
set@text='ABBBCBBBDBBBE'set@str='BBB'selectlen(replace(@text,@str,@str+'_')) -len(@text)

二. 同一个字符/字符串，第N次出现的位置

SQL SERVER定位字符位置的函数为CHARINDEX：

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

可以从指定位置起开始检索，但是不能取第N次出现的位置，需要自己写SQL来补充，有以下几种思路：

1. 自定义函数, 循环中每次为charindex加一个计数，直到为N

ifobject_id('NthChar','FN') isnotnulldropfunction Nthchar
GOcreatefunction NthChar
(
@source_stringasnvarchar(4000), 
@sub_stringasnvarchar(1024),
@nthasint) 
returnsintasbegindeclare@postionintdeclare@countintset@postion=CHARINDEX(@sub_string, @source_string) 
    set@count=0while@postion>0beginset@count=@count+1if@count=@nthbeginbreakendset@postion=CHARINDEX(@sub_string, @source_string, @postion+1) 
    Endreturn@postionendGO--select dbo.NthChar('abcabc','abc',2)--4

2. 通过CTE，对待处理的整个表字段操作, 递归中每次为charindex加一个计数，直到为N

ifobject_id('tempdb..#T') isnotnulldroptable #T

createtable #T
(
source_string nvarchar(4000)
)

insertinto #T values (N'我们我们')
insertinto #T values (N'我我哦我')

declare@sub_stringnvarchar(1024)
declare@nthintset@sub_string= N'我们'set@nth=2
;with T(source_string, starts, pos, nth) 
as (
    select source_string, 1, charindex(@sub_string, source_string), 1from #t
    unionallselect source_string, pos +1, charindex(@sub_string, source_string, pos +1), nth+1from T
    where pos >0)
select
    source_string, pos, nth
from T
where pos <>0and nth =@nthorderby source_string, starts

--source_string    pos    nth--我们我们    3    2

3. 借助数字表 (tally table)，到不同起点位置去做charindex，需要先自己构造个数字表

--numbers/tally tableIFEXISTS (select*from dbo.sysobjects where id =object_id(N'[dbo].[Numbers]') andOBJECTPROPERTY(id, N'IsUserTable') =1)
    DROPTABLE dbo.Numbers

--===== Create and populate the Tally table on the flySELECTTOP1000000IDENTITY(int,1,1) ASnumberINTO dbo.Numbers
   FROM master.dbo.syscolumns sc1,
        master.dbo.syscolumns sc2

--===== Add a Primary Key to maximize performanceALTERTABLE dbo.Numbers
        ADDCONSTRAINT PK_numbers_number PRIMARYKEYCLUSTERED (number)

--===== Allow the general public to use itGRANTSELECTON dbo.Numbers TOPUBLIC--以上数字表创建一次即可，不需要每次都重复创建DECLARE@source_stringnvarchar(4000), 
        @sub_stringnvarchar(1024), 
        @nthintSET@source_string='abcabcvvvvabc'SET@sub_string='abc'SET@nth=2
;WITH T 
AS(            
SELECT ROW_NUMBER() OVER(ORDERBYnumber) AS nth,
       numberAS[Position In String]FROM dbo.Numbers n 
 WHERE n.number<=LEN(@source_string)    
   ANDCHARINDEX(@sub_string, @source_string, n.number)-number=0----OR--AND SUBSTRING(@source_string,number,LEN(@sub_string)) = @sub_string) 
SELECT*FROM T WHERE nth =@nth

4. 通过CROSS APPLY结合charindex，适用于N值较小的时候，因为CROSS APPLY的次数要随着N的变大而增加，语句也要做相应的修改

declare@Ttable(
source_string nvarchar(4000)
)

insertinto@Tvalues('abcabc'),
('abcabcvvvvabc')

declare@sub_stringnvarchar(1024)
set@sub_string='abc'select source_string,
       p1.pos as no1,
       p2.pos as no2,
       p3.pos as no3
from@Tcross apply (select (charindex(@sub_string, source_string))) as P1(Pos)
cross apply (select (charindex(@sub_string, source_string, P1.Pos+1))) as P2(Pos)
cross apply (select (charindex(@sub_string, source_string, P2.Pos+1))) as P3(Pos)

5. 在SSIS里有内置的函数，但T-SQL中并没有

--FINDSTRING in SQL Server 2005 SSISFINDSTRING([yourColumn], "|", 2),

--TOKEN in SQL Server 2012 SSISTOKEN(Col1,"|",3)

注：不难发现，这些方法和字符串拆分的逻辑是类似的，只不过一个是定位，一个是截取，如果要获取第N个字符左右的一个/多个字符，有了N的位置，再结合substring去截取即可；

三. 多个相同字符连续，合并为一个字符

最常见的就是把多个连续的空格合并为一个空格，解决思路有两个：

1. 比较容易想到的就是用多个replace

但是究竟需要replace多少次并不确定，所以还得循环多次才行

--把两个连续空格替换成一个空格，然后循环，直到charindex检查不到两个连续空格declare@strvarchar(100)
set@str='abc        abc     kljlk     kljkl'while(charindex('',@str)>0)
beginselect@str=replace(@str,'','')
endselect@str

2. 按照空格把字符串拆开

对每一段拆分开的字符串trim或者replace后，再用一个空格连接，有点繁琐，没写代码示例，如何拆分字符串可参考：“第N次出现的位置”；

四. 是否为有效IP/身份证号/手机号等

类似IP/身份证号/手机号等这些字符串，往往都有自身特定的规律，通过substring去逐位或逐段判断是可以的，但SQL语句的方式往往性能不佳，建议尝试正则函数，见下。

五. 正则表达式函数