• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    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函数,如果有高级语言开发能力,可以自行开发;或者直接使用一些开源贡献也行,比如:http://devnambi.com/2016/sql-server-regex/

    小结:

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

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

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

    总结

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

    您可能感兴趣的文章:
    • SQL Server中利用正则表达式替换字符串的方法
    • mysql 正则表达式查询含有非数字和字符的记录
    • MySQL 字符串模式匹配 扩展正则表达式模式匹配
    上一篇:SQLserver排序规则基本概念探索
    下一篇:详解SQL Server表和索引存储结构
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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