SqlServer类似正则表达式的字符处理问题

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

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

2. 同一个字符，第n次出现的位置

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

4. 是否为有效ip/身份证号/手机号等 

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

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

declare @text varchar(1000)
declare @str varchar(10)
set @text = 'abcbdbe'
set @str = 'b'
select len(@text) - len(replace(@text,@str,''))

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

--方法1
declare @text varchar(1000)
declare @str varchar(10)
set @text = 'abbbcbbbdbbbe'
set @str = 'bbb'
select (len(@text) - len(replace(@text,@str,'')))/len(@str)
--方法2
declare @text varchar(1000)
declare @str varchar(10)
set @text = 'abbbcbbbdbbbe'
set @str = 'bbb'
select len(replace(@text,@str,@str+'_')) - len(@text)

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

sql server定位字符位置的函数为charindex：

charindex ( expressiontofind , expressiontosearch [ , start_location ] )

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

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

if object_id('nthchar','fn') is not null
  drop function nthchar
go
create function nthchar
(
@source_string as nvarchar(4000), 
@sub_string  as nvarchar(1024),
@nth      as int
) 
returns int 
as 
begin 
  declare @postion int 
  declare @count  int 
  set @postion = charindex(@sub_string, @source_string) 
  set @count = 0 
  while @postion > 0 
  begin 
    set @count = @count + 1 
    if @count = @nth 
    begin 
      break 
    end
    set @postion = charindex(@sub_string, @source_string, @postion + 1) 
  end 
  return @postion 
end 
go
--select dbo.nthchar('abcabc','abc',2)
--4

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

if object_id('tempdb..#t') is not null
  drop table #t
create table #t
(
source_string nvarchar(4000)
)
insert into #t values (n'我们我们')
insert into #t values (n'我我哦我')
declare @sub_string nvarchar(1024)
declare @nth    int
set @sub_string = n'我们'
set @nth = 2
;with t(source_string, starts, pos, nth) 
as (
  select source_string, 1, charindex(@sub_string, source_string), 1 from #t
  union all
  select source_string, pos + 1, charindex(@sub_string, source_string, pos + 1), nth+1 from t
  where pos > 0
)
select 
  source_string, pos, nth
from t
where pos <> 0
 and nth = @nth
order by source_string, starts
--source_string  pos  nth
--我们我们  3  2

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

--numbers/tally table
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[numbers]') and objectproperty(id, n'isusertable') = 1)
  drop table dbo.numbers
--===== create and populate the tally table on the fly
 select top 1000000 
    identity(int,1,1) as number
  into dbo.numbers
  from master.dbo.syscolumns sc1,
    master.dbo.syscolumns sc2
--===== add a primary key to maximize performance
 alter table dbo.numbers
    add constraint pk_numbers_number primary key clustered (number)
--===== allow the general public to use it
 grant select on dbo.numbers to public
--以上数字表创建一次即可，不需要每次都重复创建
declare @source_string  nvarchar(4000), 
    @sub_string    nvarchar(1024), 
    @nth       int
set @source_string = 'abcabcvvvvabc'
set @sub_string = 'abc'
set @nth = 2 
;with t 
as
(      
select row_number() over(order by number) as nth,
    number as [position in string]
 from dbo.numbers n 
 where n.number <= len(@source_string)  
  and charindex(@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 @t table
(
source_string nvarchar(4000)
)

insert into @t values
('abcabc'),
('abcabcvvvvabc')
declare @sub_string nvarchar(1024)
set @sub_string = 'abc'

select source_string,
    p1.pos as no1,
    p2.pos as no2,
    p3.pos as no3
from @t
cross 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 ssis
findstring([yourcolumn], "|", 2),
--token in sql server 2012 ssis
token(col1,"|",3)

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

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

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

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

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

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

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

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

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

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

五. 正则表达式函数

1. oracle

从10g开始，可以在查询中使用正则表达式，它通过一些支持正则表达式的函数来实现：

oracle 10 g
regexp_like
regexp_replace
regexp_instr
regexp_substr
oracle 11g (新增)
regexp_count

oracle用regexp函数处理上面几个问题：

(1) 同一个字符/字符串，出现了多少次

select length(regexp_replace('123-345-566', '[^-]', '')) from dual;
select regexp_count('123-345-566', '-') from dual; --oracle 11g 

(2) 同一个字符/字符串，第n次出现的位置

不需要正则，oracle的instr可以直接查找位置：

instr('source_string','sub_string' [,n][,m])

n表示从第n个字符开始搜索，缺省值为1，m表示第m次出现，缺省值为1。

select instr('abcdefghijkabc','abc', 1, 2) position from dual; 

(3) 多个相同字符连续，合并为一个字符

select regexp_replace(trim('agc f  f '),'\s+',' ') from dual; 

(4) 是否为有效ip/身份证号/手机号等

--是否为有效ip
with ip
as(
select '10.20.30.40' ip_address from dual union all
select 'a.b.c.d' ip_address from dual union all
select '256.123.0.254' ip_address from dual union all
select '255.255.255.255' ip_address from dual
)
select *
from ip
where regexp_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. sql server

目前最新版本为sql server 2017，还没有对regexp函数的支持，需要通用clr来扩展，如下为clr实现reg_replace：

--1. 开启 clr 
exec sp_configure 'show advanced options' , '1'
go
reconfigure
go
exec sp_configure 'clr enabled' , '1'
go
reconfigure
go
exec sp_configure 'show advanced options' , '0';
go

 2. 创建 assembly

--3. 创建 clr 函数
create function [dbo].[regex_replace](@input [nvarchar](4000), @pattern [nvarchar](4000), @replacement [nvarchar](4000))
returns [nvarchar](4000) with execute as caller, returns null on null input
as 
external name [regexutility].[regexutility].[regexreplacedefault]
go
--4. 使用regex_replace替换多个空格为一个空格
select dbo.regex_replace('agc f  f ','\s+',' ');

注：通过clr实现更多regexp函数，如果有高级语言开发能力，可以自行开发；或者直接使用一些开源贡献也行，比如：

小结：

1. 非正则sql语句的思路，对不同数据库往往都适用；

2. 正则表达式中的规则(pattern) 在不同开发语言里，有很多语法是相通的，通常是遵守perl或者linux shell中的sed等工具的规则；

3. 从性能上来看，通用sql判断 > regexp函数 > 自定义sql函数。

总结

以上所述是小编给大家介绍的sqlserver类似正则表达式的字符处理问题，希望对大家有所帮助