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

数据库 2024/11/16 佚名

3 2 1

常春岛资源网 Design By www.syssdc.com

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函数，如果有高级语言开发能力，可以自行开发；或者直接使用一些开源贡献也行，比如：http://devnambi.com/2016/sql-server-regex/

小结：

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

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

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

总结

以上所述是小编给大家介绍的SqlServer类似正则表达式的字符处理问题，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！

sql,正则表达式字符

标签：

sql,正则表达式字符

常春岛资源网 Design By www.syssdc.com

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

常春岛资源网 Design By www.syssdc.com

评论“SqlServer类似正则表达式的字符处理问题”

暂无评论...

www.syssdc.com 常春岛资源网

139,976互联网资源

144,792高清电影

21,817无损音乐

631,128技术资源

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

sql,正则表达式字符

详解SQL Server表和索引存储结构

SQLserver排序规则基本概念探索

评论“SqlServer类似正则表达式的字符处理问题”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

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

sql,正则表达式字符

详解SQL Server表和索引存储结构

SQLserver排序规则基本概念探索

评论“SqlServer类似正则表达式的字符处理问题”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存