SqlServer类似正则表达式的字符处理是怎样的

共计 6155 个字符，预计需要花费 16 分钟才能阅读完成。

SqlServer 类似正则表达式的字符处理是怎样的，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

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

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

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

declare @text varchar(1000)declare @str varchar(10)set @text =  ABCBDBE set @str =  B select len(@text) - len(replace(@text,@str,))

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

-- 方法 1declare @text varchar(1000)declare @str varchar(10)set @text =  ABBBCBBBDBBBE set @str =  BBB select (len(@text) - len(replace(@text,@str,)))/len(@str)-- 方法 2declare @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 NthcharGOcreate 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 #Tcreate table #T(source_string nvarchar(4000))insert into #T values (N 我们我们)insert into #T values (N 我我哦我)declare @sub_string nvarchar(1024)declare @nth intset @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, nthfrom Twhere pos   0 and nth = @nthorder by source_string, starts--source_string pos nth-- 我们我们  3 2

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

--numbers/tally tableIF 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 intSET @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 no3from @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 @str varchar(100)set @str= abc abc kljlk kljkl while(charindex(   ,@str) 0)begin select @str=replace(@str,   ,  )endselect @str

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

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

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

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

五. 正则表达式函数

1. Oracle

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

Oracle 10 gREGEXP_LIKEREGEXP_REPLACEREGEXP_INSTRREGEXP_SUBSTROracle 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/ 身份证号 / 手机号等

-- 是否为有效 IPWITH IPAS(SELECT  10.20.30.40  ip_address FROM dual UNION ALLSELECT  a.b.c.d  ip_address FROM dual UNION ALLSELECT  256.123.0.254  ip_address FROM dual UNION ALLSELECT  255.255.255.255  ip_address FROM dual)SELECT *FROM IPWHERE 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 GORECONFIGUREGOEXEC sp_configure  clr enabled  ,  1 GORECONFIGUREGOEXEC 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 INPUTAS 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 类似正则表达式的字符处理是怎样的的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！