【997755.com澳门葡京】正则表明式在ORACLE中的使用,类似正则表明式的字符管理难题

SQL Serve提供了简便易行的字符模糊相称作用,比方:like,
patindex,然而对此有些字符管理场景还显示并不充裕,平常碰到的多少个难题有:

SQL Serve提供了总结的字符模糊相配功效,比方:like,
patindex,可是对于一些字符处理场景还显得并不丰裕,平常境遇的多少个难点有:

Oracle使用正则表明式离不开那伍个函数:

Oracle使用正则表达式离不开那5个函数:

  • 1. 同一个字符/字符串,出现了有点次
  • 2. 同2个字符,第N次出现的地方
  • 3. 多少个同样字符接二连三,合并为3个字符
  • 4. 是还是不是为使得IP/身份证号/手提式有线话机号等
  • 1. 同四个字符/字符串,出现了稍稍次
  • 2. 同1个字符,第N次现身的任务
  • 3. 多个同样字符两次三番,合并为二个字符
  • 4. 是还是不是为有效IP/身份证号/手提式有线电话机号等

1。regexp_like

1。regexp_like

 

 

2。regexp_substr

2。regexp_substr

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

一.
同三个字符/字符串,现身了略微次

3。regexp_instr

3。regexp_instr

同3个字符,将其替换为空白,就可以测算

同多少个字符,将其替换为空白,就可以测算

4。regexp_replace

4。regexp_replace

declare @text varchar(1000)
declare @str  varchar(10)
set @text = 'ABCBDBE'
set @str = 'B'

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

select len(@text) - len(replace(@text,@str,''))

看函数名称大约就能猜到有何用了。

看函数名称大约就能猜到有怎么着用了。

同3个字符串,如故是替换,因为是七个字符,方法1轮换后供给做3遍除法;方法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)
--方法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)

regexp_like 只可以用来标准表达式,和 like
类似,但是利用的正则表明式举行相配,语法相当粗略:

regexp_like 只可以用来标准表明式,和 like
类似,可是采取的正则表明式进行般配,语法很简单:

 

 

 

 

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

二.
同1个字符/字符串,第N次出现的义务

regexp_substr 函数,和 substr
类似,用于10取合符正则表明式描述的字符子串,语法如下:

regexp_substr 函数,和 substr
类似,用于十取合符正则表明式描述的字符子串,语法如下:

SQL SE奇骏VEOdyssey定位字符地方的函数为CHAENCOREINDEX:

SQL SE奥德赛VE揽胜定位字符地方的函数为CHAXC90INDEX:

regexp_instr 函数,和 instr
类似,用于标定符合正则表明式的字符子串的初步地方,语法如下:

regexp_instr 函数,和 instr
类似,用于标定符合正则表明式的字符子串的启幕地方,语法如下:

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location
] )

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location
] )

 

 

能够从钦定地点起初叶索求,不过不可能取第N次出现的职位,须要团结写SQL来补偿,有以下二种思路:

能够从钦点地点起开头物色,不过无法取第N次出现的岗位,需求团结写SQL来补偿,有以下二种思路:

regexp_replace 函数,和 replace
类似,用于替换符合正则表明式的字符串,语法如下:

regexp_replace 函数,和 replace
类似,用于替换符合正则表明式的字符串,语法如下:

1. 自定义函数, 循环中年老年是为charindex加四个计数,直到为N

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
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

此地解析一下多少个参数的意义:

那里解析一下多少个参数的含义:

 

 

1。source_char,输入的字符串,能够是列名也许字符串常量、变量。

1。source_char,输入的字符串,能够是列名恐怕字符串常量、变量。

二. 经过CTE,对待管理的百分之百表字段操作,
递归中年老年是为charindex加一个计数,直到为N

二. 透过CTE,对待管理的凡事表字段操作,
递归中年老年是为charindex加1个计数,直到为N

二。pattern,正则表达式。

2。pattern,正则表达式。

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
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。match_parameter,匹配选项。

3。match_parameter,相称选项。

 

 

        取值范围: i:大小写不灵动; c:大小写敏感;n:点号 .
不相配换行符号;m:多行形式;x:扩充方式,忽略正则表明式中的空白字符。

        取值范围: i:大小写不敏感; c:大小写敏感;n:点号 .
不相称换行符号;m:多行方式;x:扩充格局,忽略正则表明式中的空白字符。

3. 借助于数字表 (tally
table),到分化起源地点去做charindex,要求先本人组织个数字表

3. 借助数字表 (tally
table),到不一致源点地点去做charindex,必要先本人协会个数字表

四。position,标志从第几个字符起始正则表明式相称。

四。position,标志从第多少个字符起始正则表达式相称。

--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
--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

5。occurrence,标志第多少个相配组。

5。occurrence,标记第多少个相称组。

 

 

6。replace_string,替换的字符串。

6。replace_string,替换的字符串。

4. 经过CROSS 应用软件LY结合charindex,适用于N值不大的时候,因为CROSS
APPLY的次数要随着N的变大而充实,语句也要做相应的修改

4. 因此CROSS 应用程式LY结合charindex,适用于N值相当小的时候,因为CROSS
应用软件LY的次数要随着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)
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)

说了一批文绉绉的,未来开首实例练习了,以前先建好三个表。

【997755.com澳门葡京】正则表明式在ORACLE中的使用,类似正则表明式的字符管理难题。说了一批文绉绉的,今后伊始实例练习了,以前先建好3个表。

 

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

create table tmp as

with data as (

  select ‘like’ as id ,’a9999′ as str from dual union all

  select ‘like’       ,’a9c’          from dual union all

  select ‘like’       ,’A7007′        from dual union all

  select ‘like’       ,’123a34cc’     from dual union all

  select ‘substr’     ,’123,234,345′  from dual union all

  select ‘substr’     ,’12,34.56:78′  from dual union all

  select ‘substr’     ,’123456789′    from dual union all

  select ‘instr’      ,’192.168.0.1′  from dual union all

  select ‘replace’    ,'(020)12345678′ from dual union all

  select ‘replace’    ,’001517729C28′ from dual 

)

select * from data ;

 

select * from tmp ;

ID      STR

——- ————-

like    a9999

like    a9c

like    A7007

like    123a34cc

substr  123,234,345

substr  12,34.56:78

substr  123456789

instr   192.168.0.1

replace (020)12345678

replace 001517729C28

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

create table tmp as

with data as (

  select ‘like’ as id ,’a9999′ as str from dual union all

  select ‘like’       ,’a9c’          from dual union all

  select ‘like’       ,’A7007′        from dual union all

  select ‘like’       ,’123a34cc’     from dual union all

  select ‘substr’     ,’123,234,345′  from dual union all

  select ‘substr’     ,’12,34.56:78′  from dual union all

  select ‘substr’     ,’123456789′    from dual union all

  select ‘instr’      ,’192.168.0.1′  from dual union all

  select ‘replace’    ,'(020)12345678′ from dual union all

  select ‘replace’    ,’001517729C28′ from dual 

)

select * from data ;

 

select * from tmp ;

ID      STR

——- ————-

like    a9999

like    a9c

like    A7007

like    123a34cc

substr  123,234,345

substr  12,34.56:78

substr  123456789

instr   192.168.0.1

replace (020)12345678

replace 001517729C28

5. 在SSIS里有停放的函数,但T-SQL中并从未

5. 在SSIS里有内置的函数,但T-SQL中并从未

regexp_like 例子:

regexp_like 例子:

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

--TOKEN in SQL Server 2012 SSIS
TOKEN(Col1,"|",3)
--FINDSTRING in SQL Server 2005 SSIS
FINDSTRING([yourColumn], "|", 2),

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

select str from tmp where id=’like’ and regexp_like(str,’A\d+’,’i’); — ‘i’ 忽略大小写

STR

————-

a9999

a9c

A7007

123a34cc

 

select str from tmp where id=’like’ and regexp_like(str, ‘a\d+’);

STR

————-

a9999

a9c

123a34cc

 

select str from tmp where id=’like’ and regexp_like(str,’^a\d+’);

STR

————-

a9999

a9c

 

select str from tmp where id=’like’ and regexp_like(str,’^a\d+$’);

STR

————-

a9999

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

select str from tmp where id=’like’ and regexp_like(str,’A\d+’,’i’); — ‘i’ 忽略大小写

STR

————-

a9999

a9c

A7007

123a34cc

 

select str from tmp where id=’like’ and regexp_like(str, ‘a\d+’);

STR

————-

a9999

a9c

123a34cc

 

select str from tmp where id=’like’ and regexp_like(str,’^a\d+’);

STR

————-

a9999

a9c

 

select str from tmp where id=’like’ and regexp_like(str,’^a\d+$’);

STR

————-

a9999

 

 

regexp_substr 例子:

regexp_substr 例子:

注:轻易察觉,那个措施和字符串拆分的逻辑是类似的,只不过多少个是定点,二个是截取,假诺要获取第N个字符左右的3个/五个字符,有了N的职分,再结合substring去截取就能够;

注:简单窥见,那一个情势和字符串拆分的逻辑是看似的,只然则三个是定点,二个是截取,如若要获取第N个字符左右的1个/多少个字符,有了N的职位,再结合substring去截取就可以;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

col str format a15;

select

  str,

  regexp_substr(str,'[^,]+’)     str,

  regexp_substr(str,'[^,]+’,1,1) str,

  regexp_substr(str,'[^,]+’,1,2) str,  — occurrence 第几个匹配组

  regexp_substr(str,'[^,]+’,2,1) str   — position 从第几个字符开始匹配

from tmp

where id=’substr’;

STR             STR             STR             STR             STR

————— ————— ————— ————— —————

123,234,345     123             123             234             23

12,34.56:78     12              12              34.56:78        2

123456789       123456789       123456789                       23456789

 

select

  str,

  regexp_substr(str,’\d’)        str,

  regexp_substr(str,’\d+’  ,1,1) str,

  regexp_substr(str,’\d{2}’,1,2) str,

  regexp_substr(str,’\d{3}’,2,1) str

from tmp     

where id=’substr’;

STR             STR             STR             STR             STR

————— ————— ————— ————— —————

123,234,345     1               123             23              234

12,34.56:78     1               12              34

123456789       1               123456789       34              234

 

 

select regexp_substr(‘123456789′,’\d’,1,level) str  –取出每位数字,有时这也是行转列的方式

from dual

connect by level<=9

STR

—————

1

2

3

4

5

6

7

8

9

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

col str format a15;

select

  str,

  regexp_substr(str,'[^,]+’)     str,

  regexp_substr(str,'[^,]+’,1,1) str,

  regexp_substr(str,'[^,]+’,1,2) str,  — occurrence 第几个匹配组

  regexp_substr(str,'[^,]+’,2,1) str   — position 从第几个字符开始匹配

from tmp

where id=’substr’;

STR             STR             STR             STR             STR

————— ————— ————— ————— —————

123,234,345     123             123             234             23

12,34.56:78     12              12              34.56:78        2

123456789       123456789       123456789                       23456789

 

select

  str,

  regexp_substr(str,’\d’)        str,

  regexp_substr(str,’\d+’  ,1,1) str,

  regexp_substr(str,’\d{2}’,1,2) str,

  regexp_substr(str,’\d{3}’,2,1) str

from tmp     

where id=’substr’;

STR             STR             STR             STR             STR

————— ————— ————— ————— —————

123,234,345     1               123             23              234

12,34.56:78     1               12              34

123456789       1               123456789       34              234

 

 

select regexp_substr(‘123456789′,’\d’,1,level) str  –取出每位数字,有时这也是行转列的方式

from dual

connect by level<=9

STR

—————

1

2

3

4

5

6

7

8

9

 

 

regex_instr 例子:

regex_instr 例子:

三.
多少个同样字符一连,合并为三个字符

三.
八个一样字符一而再,合并为多个字符

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

col ind format 9999;

select

  str,

  regexp_instr(str,’\.’    ) ind ,

  regexp_instr(str,’\.’,1,2) ind ,

  regexp_instr(str,’\.’,5,2) ind

from tmp where id=’instr’;

STR               IND   IND   IND

————— —– —– —–

192.168.0.1         4     8    10

     

select

  regexp_instr(‘192.168.0.1′,’\.’,1,level) ind ,  — 点号. 所在的位置

  regexp_instr(‘192.168.0.1′,’\d’,1,level) ind    — 每个数字的位置

from dual

connect by level <=  9

  IND   IND

—– —–

    4     1

    8     2

   10     3

    0     5

    0     6

    0     7

    0     9

    0    11

    0     0

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

col ind format 9999;

select

  str,

  regexp_instr(str,’\.’    ) ind ,

  regexp_instr(str,’\.’,1,2) ind ,

  regexp_instr(str,’\.’,5,2) ind

from tmp where id=’instr’;

STR               IND   IND   IND

————— —– —– —–

192.168.0.1         4     8    10

     

select

  regexp_instr(‘192.168.0.1′,’\.’,1,level) ind ,  — 点号. 所在的位置

  regexp_instr(‘192.168.0.1′,’\d’,1,level) ind    — 每个数字的位置

from dual

connect by level <=  9

  IND   IND

—– —–

    4     1

    8     2

   10     3

    0     5

    0     6

    0     7

    0     9

    0    11

    0     0

最广大的就是把四个接二连三的空格合并为贰个空格,消除思路有多少个:

最广大的正是把多少个一连的空格合并为二个空格,消除思路有多个:

regex_replace 例子:

regex_replace 例子:

1. 正如便于想到的正是用三个replace

997755.com澳门葡京 ,1. 正如便于想到的就是用三个replace

1

2

3

4

5

6

7

8

9

10

select

  str,

  regexp_replace(str,’020′,’GZ’) str,

  regexp_replace(str,'(\d{3})(\d{3})’,'<\2\1>’) str — 将第一、第二捕获组交换位置,用尖括号标识出来

from tmp

where id=’replace’; 

STR             STR             STR

————— ————— —————

(020)12345678   (GZ)12345678    (020)<456123>78

001517729C28    001517729C28    <517001>729C28

1

2

3

4

5

6

7

8

9

10

select

  str,

  regexp_replace(str,’020′,’GZ’) str,

  regexp_replace(str,'(\d{3})(\d{3})’,'<\2\1>’) str — 将第一、第二捕获组交换位置,用尖括号标识出来

from tmp

where id=’replace’; 

STR             STR             STR

————— ————— —————

(020)12345678   (GZ)12345678    (020)<456123>78

001517729C28    001517729C28    <517001>729C28

不过到底要求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
--把两个连续空格替换成一个空格,然后循环,直到charindex检查不到两个连续空格
declare @str varchar(100)
set @str='abc        abc     kljlk     kljkl'
while(charindex('  ',@str)>0)
begin
    select @str=replace(@str,'  ',' ')
end
select @str

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

col row_line format a30;

with sudoku as (

  select ‘020000080568179234090000010030040050040205090070080040050000060289634175010000020’ as line

  from dual

),

tmp as (

  select regexp_substr(line,’\d{9}’,1,level) row_line,

  level col

  from sudoku

  connect by level<=9

)

select regexp_replace( row_line ,'(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)’,’\1 \2 \3 \4 \5 \6 \7 \8 \9′) row_line

from tmp

 

ROW_LINE

——————————

0 2 0 0 0 0 0 8 0

5 6 8 1 7 9 2 3 4

0 9 0 0 0 0 0 1 0

0 3 0 0 4 0 0 5 0

0 4 0 2 0 5 0 9 0

0 7 0 0 8 0 0 4 0

0 5 0 0 0 0 0 6 0

2 8 9 6 3 4 1 7 5

0 1 0 0 0 0 0 2 0

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

col row_line format a30;

with sudoku as (

  select ‘020000080568179234090000010030040050040205090070080040050000060289634175010000020’ as line

  from dual

),

tmp as (

  select regexp_substr(line,’\d{9}’,1,level) row_line,

  level col

  from sudoku

  connect by level<=9

)

select regexp_replace( row_line ,'(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)’,’\1 \2 \3 \4 \5 \6 \7 \8 \9′) row_line

from tmp

 

ROW_LINE

——————————

0 2 0 0 0 0 0 8 0

5 6 8 1 7 9 2 3 4

0 9 0 0 0 0 0 1 0

0 3 0 0 4 0 0 5 0

0 4 0 2 0 5 0 9 0

0 7 0 0 8 0 0 4 0

0 5 0 0 0 0 0 6 0

2 8 9 6 3 4 1 7 5

0 1 0 0 0 0 0 2 0

 

 

 

 

2. 遵循空格把字符串拆开

2. 根据空格把字符串拆开

 

 

对每1段拆分开的字符串trim也许replace后,再用三个空格连接,有点麻烦,没写代码示例,怎么着拆分字符串可参考:“第N次面世的地方”;

对每一段拆分开的字符串trim恐怕replace后,再用三个空格连接,有点麻烦,没写代码示例,怎样拆分字符串可参看:“第N次出现的职位”;

oracle的正则表明式(regular expression)简介 
脚下,正则表明式已经在繁多软件中获得普遍的行使,包含*nix(Linux,
Unix等),HP等操作系统,PHP,C#,Java等开支情形。 
Oracle
10g正则表明式进步了SQL灵活性。有效的化解了数占有效性,重复词的甄别,
非亲非故的空域检验,或然解释几个正则组成的字符串等主题素材。 
Oracle
10g支撑正则表明式的多个新函数分别是:REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和REGEXP_REPLACE。 
它们利用POSIX 正则表明式代替了老的百分号(%)和通配符(_)字符。 
特殊字符: 
‘^’
匹配输入字符串的启幕地方,在方括号表明式中动用,此时它代表不接受该字符集合。 
‘$’ 相称输入字符串的结尾地方。倘若设置了 RegExp 对象的 Multiline
属性,则 $ 也至极 ‘\n’ 或’\r’。 
‘.’ 匹配除换行符 \n之外的其他单字符。 
‘?’ 相配后面包车型客车子表明式零次或三次。 
‘*’ 相配前边的子表达式零次或频仍。 
‘+’ 相称后面包车型客车子表明式三回或频仍。 
‘( )’ 标识3个子表明式的启幕和终止地点。 
‘[]’ 标识三个中括号表达式。 
‘{m,n}’
二个标准地面世次数限制,m=<出现次数<=n,'{m}’表示出现m次,'{m,}’表示至少出现m次。 
‘|’
指明两项之间的三个摘取。例子’^([a-z]+|[0-9]+)$’代表全数小写字母或数字组合成的字符串。 
\num 相称 num,个中 num 是二个正整数。对所获得的合作的引用。 
正则表明式的二个很有用的特点是足以保存子表明式未来选择,被喻为Backreferencing.
允许复杂的轮换手艺 
如调解3个情势到新的职务仍旧提醒被代表的字符或然单词的地点.
被相称的子表明式存款和储蓄在目前缓冲区 

oracle的正则表达式(regular expression)简介 
日前,正则表明式已经在广大软件中赚取大面积的采取,包蕴*nix(Linux,
Unix等),HP等操作系统,PHP,C#,Java等支出碰到。 
Oracle
十g正则表明式进步了SQL灵活性。有效的缓和了数量有效性,重复词的甄别,
非亲非故的空域检查评定,可能解释七个正则组成的字符串等主题素材。 
Oracle
拾g支撑正则表明式的多个新函数分别是:REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和REGEXP_REPLACE。 
它们选择POSIX 正则说明式代替了老的百分号(%)和通配符(_)字符。 
特殊字符: 
‘^’
相配输入字符串的开首地点,在方括号表达式中利用,此时它代表不收受该字符集结。 
‘$’ 相配输入字符串的终极地方。倘使设置了 RegExp 对象的 Multiline
属性,则 $ 也相配 ‘\n’ 或’\r’。 
‘.’ 相称除换行符 \n之外的别的单字符。 
‘?’ 相配前边的子表明式零次或一遍。 
‘*’ 相称前边的子表明式零次或频仍。 
‘+’ 相配前边的子表明式一遍或频仍。 
‘( )’ 标志3个子表明式的开端和甘休地方。 
‘[]’ 标识1当中括号表明式。 
‘{m,n}’
1个准儿地出现次数限制,m=<出现次数<=n,'{m}’表示出现m次,'{m,}’表示至少出现m次。 
‘|’
指明两项之间的一个精选。例子’^([a-z]+|[0-9]+)$’代表全体小写字母或数字组合成的字符串。 
\num 相配 num,个中 num 是贰个正整数。对所获得的相称的引用。 
正则表达式的2个很有用的特点是足以保存子表明式今后使用,被号称Backreferencing.
允许复杂的替换技能 
如调节2个格局到新的职位照旧提醒被代替的字符或许单词的地方.
被相称的子表明式存款和储蓄在一时半刻缓冲区 

 

 

中,缓冲区从左到右编号, 通过\数字符号访问。 上边包车型地铁例子列出了把名字 aa
bb cc 造成cc, bb, aa. 
Select REGEXP_REPLACE(‘aa bb cc’,'(.*) (.*) (.*)’, ‘\3, \2, \1’)
FROM dual; 
REGEXP_REPLACE(‘ELLENHILDISMIT 
cc, bb, aa 
‘\’ 转义符。 
字符簇: 
[[:alpha:]] 任何字母。 
[[:digit:]] 任何数字。 
[[:alnum:]] 任何字母和数字。 
[[:space:]] 任何白字符。 
[[:upper:]] 任何大写字母。 
[[:lower:]] 任何小写字母。 
[[unct:]] 任何标点符号。 
[[:xdigit:]] 任何1陆进制的数字,也就是[0-9a-fA-F]。 
各样操作符的演算优先级 
\ 转义符 
(), (?, (?=), [] 圆括号和方括号 
*, +, ?, {n}, {n,}, {n,m} 限定符 
^, $, \anymetacharacter 地方和11 
| “或”操作 

中,缓冲区从左到右编号, 通过\数字符号访问。 下边的事例列出了把名字 aa
bb cc 产生cc, bb, aa. 
Select REGEXP_REPLACE(‘aa bb cc’,'(.*) (.*) (.*)’, ‘\3, \2, \1’)
FROM dual; 
REGEXP_REPLACE(‘ELLENHILDISMIT 
cc, bb, aa 
‘\’ 转义符。 
字符簇: 
[[:alpha:]] 任何字母。 
[[:digit:]] 任何数字。 
[[:alnum:]] 任何字母和数字。 
[[:space:]] 任何白字符。 
[[:upper:]] 任何大写字母。 
[[:lower:]] 任何小写字母。 
[[unct:]] 任何标点符号。 
[[:xdigit:]] 任何1陆进制的数字,也便是[0-9a-fA-F]。 
种种操作符的运算优先级 
\ 转义符 
(), (?, (?=), [] 圆括号和方括号 
*, +, ?, {n}, {n,}, {n,m} 限定符 
^, $, \anymetacharacter 地方和一1 
| “或”操作 

四.
是或不是为使得IP/身份证号/手提式有线电话机号等

四.
是否为可行IP/身份证号/手提式有线电话机号等

接近IP/身份证号/手提式有线电话机号等这几个字符串,往往都有自家特定的原理,通过substring去逐位或逐段推断是能够的,但SQL语句的格局往往质量不好,提出尝试正则函数,见下。

类似IP/身份证号/手提式有线电话机号等这几个字符串,往往都有自己特定的法则,通过substring去逐位或逐段剖断是足以的,但SQL语句的措施往往品质不好,提出尝试正则函数,见下。

 

 

五. 正则表明式函数

五. 正则表达式函数

1. Oracle

1. Oracle

从10g起来,能够在查询中选择正则表明式,它经过一些支撑正则表明式的函数来促成:

从10g初阶,可以在询问中采取正则表明式,它通过某些协理正则表明式的函数来促成:

Oracle 10 g

Oracle 10 g

REGEXP_LIKE

REGEXP_LIKE

REGEXP_REPLACE

REGEXP_REPLACE

REGEXP_INSTR

REGEXP_INSTR

REGEXP_SUBSTR

REGEXP_SUBSTR

 

 

Oracle 11g (新增)

Oracle 11g (新增)

REGEXP_COUNT

REGEXP_COUNT

 

 

Oracle用REGEXP函数管理地点多少个难点:

Oracle用REGEXP函数管理地点多少个难点:

(1) 同2个字符/字符串,出现了有点次

(1) 同1个字符/字符串,出现了不怎么次

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

 

 

(2) 同二个字符/字符串,第N次面世的地方

(2) 同贰个字符/字符串,第N次出现的职位

不须要正则,ORACLE的instr能够平素寻找地方:

不需求正则,ORACLE的instr能够直接搜索地点:

instr(‘source_string’,’sub_string’ [,n][,m])

instr(‘source_string’,’sub_string’ [,n][,m])

n表示从第n个字符开端找出,缺省值为一,m表示第m次现身,缺省值为一。

n表示从第n个字符起始物色,缺省值为一,m表示第m次现身,缺省值为1。

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

 

 

(3) 多少个1律字符延续,合并为四个字符

(3) 多少个一律字符延续,合并为一个字符

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

 

 

(4) 是不是为有效IP/身份证号/手提式有线电话机号等

(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])$');
--是否为有效身份证/手机号,暂未举例
--是否为有效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

2. SQL Server

当下风靡版本为SQL Server
20一七,还尚未对REGEXP函数的援救,要求通用CL帕杰罗来扩展,如下为CL科雷傲达成REG_REPLACE:

目前流行版本为SQL Server
201柒,还未曾对REGEXP函数的支撑,必要通用CL昂Cora来扩大,如下为CL奥迪Q5落成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
--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

997755.com澳门葡京 1997755.com澳门葡京 2

997755.com澳门葡京 3997755.com澳门葡京 4

--首次创建时,应该是从dll文件创建
--CREATE ASSEMBLY [RegexUtility] FROM 'C:\xxxxxxx.dll'
--GO

--创建好后,可以生成脚本出来部署到其他支持CLR的SQL Server上
CREATE ASSEMBLY [RegexUtility]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103002073E9470000000000000000E0000E210B010800000A00000006000000000000EE2900000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300400500001000001000000000100000100000000000001000000000000000000000009C2900004F000000004000003803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000F409000000200000000A000000020000000000000000000000000000200000602E72737263000000380300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000D0290000000000004800000002000500EC200000B008000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007A0F00280E00000A0F01280E00000A280F00000A6F1000000A281100000A2A001330030047000000010000110F01280E00000A731200000A0A060F00280E00000A6F1300000A6F1000000A2C200F00280E00000A0F01280E00000A0F02280E00000A281400000A281500000A2A7E1600000A2A820F00280E00000A0F01280E00000A0F02280E00000A281400000A281500000A2A1E02281800000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000007C020000237E0000E8020000F402000023537472696E677300000000DC0500000800000023555300E4050000100000002347554944000000F4050000BC02000023426C6F620000000000000002000001471502000900000000FA0133001600000100000014000000020000000400000008000000180000000D00000001000000010000000300000000000A000100000000000600380031000A0060004B000A006B004B000600B900A7000600F100D20006000501A70006001E01A70006003901A70006005401A70006006D01A70006008601A7000600A501A7000600C201A7000600F901D90106001902D9010A00520237020E009E027F020E00A4027F020E00AA027F020A00E10237020000000001000000000001000100010010001B000000050001000100502000000000960075000A0001007020000000009600800013000300C3200000000096008D0013000600E420000000008618A1001E000900000001006702000002006D02000001006702000002006D0200000300C802000001006702000002006D0200000300C8022100A10022002900A10027003100A10022003900A10022004100A10022004900A10022005100A10022005900A10022006100A10022006900A10022007100A1002C007900A1001E008100A1001E0019007502D5008900A402D9009900B002E0001100BC02E4008900A10022008900A402EA008900D402F0001900BC02F7001900DC02FD00A100A1001E000900A1001E0020006B0031002E0033007F022E003B0079022E0063009A022E00230079022E002B0079022E00430079022E00130079022E004B0079022E0053007F022E005B00910240006B0031006000BB00060101010480000001000000BE0B28760000000000001B000000020000000000000000000000010028000000000002000000000000000000000001003F0000000000020000000000000000000000010031000000000000000000003C4D6F64756C653E0052656765785574696C6974792E646C6C0052656765785574696C697479006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E670052656765784D617463680052656765785265706C6163650052656765785265706C61636544656661756C74002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500696E707574007061747465726E006765745F56616C75650053797374656D2E546578742E526567756C617245787072657373696F6E73005265676578004D617463680047726F7570006765745F53756363657373006F705F496D706C69636974007265706C6163656D656E74005265706C616365004E756C6C0053716C4D6574686F64417474726962757465000003200000000000253F930E81E4D84AAC7FD0EB9C3404A30008B77A5C561934E0890800021109110D110D0A0003110D110D110D110D03200001042001010E0420010102042001010880A20100020054020F497344657465726D696E6973746963015455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A44617461416363657373000000000320000E06000212490E0E0320000205000111090205200112490E0600030E0E0E0E050001110D0E0306110D040701124581710100060054020F497344657465726D696E6973746963015402094973507265636973650154020A4F6E4E756C6C43616C6C005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000540E044E616D651572656765785F7265706C6163655F64656661756C740501000000001101000C52656765785574696C69747900000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000C42900000000000000000000DE290000002000000000000000000000000000000000000000000000D0290000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000E00200000000000000000000E00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001002876BE0B000001002876BE0B3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00440020000010053007400720069006E006700460069006C00650049006E0066006F0000001C020000010030003000300030003000340062003000000044000D000100460069006C0065004400650073006300720069007000740069006F006E0000000000520065006700650078005500740069006C006900740079000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003000300036002E00330030003200340038000000000044001100010049006E007400650072006E0061006C004E0061006D0065000000520065006700650078005500740069006C006900740079002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004C00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000520065006700650078005500740069006C006900740079002E0064006C006C00000000003C000D000100500072006F0064007500630074004E0061006D00650000000000520065006700650078005500740069006C006900740079000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003000300036002E00330030003200340038000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003000300036002E0033003000320034003800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000F03900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE

GO
--首次创建时,应该是从dll文件创建
--CREATE ASSEMBLY [RegexUtility] FROM 'C:\xxxxxxx.dll'
--GO

--创建好后,可以生成脚本出来部署到其他支持CLR的SQL Server上
CREATE ASSEMBLY [RegexUtility]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103002073E9470000000000000000E0000E210B010800000A00000006000000000000EE2900000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300400500001000001000000000100000100000000000001000000000000000000000009C2900004F000000004000003803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000F409000000200000000A000000020000000000000000000000000000200000602E72737263000000380300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000D0290000000000004800000002000500EC200000B008000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007A0F00280E00000A0F01280E00000A280F00000A6F1000000A281100000A2A001330030047000000010000110F01280E00000A731200000A0A060F00280E00000A6F1300000A6F1000000A2C200F00280E00000A0F01280E00000A0F02280E00000A281400000A281500000A2A7E1600000A2A820F00280E00000A0F01280E00000A0F02280E00000A281400000A281500000A2A1E02281800000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000007C020000237E0000E8020000F402000023537472696E677300000000DC0500000800000023555300E4050000100000002347554944000000F4050000BC02000023426C6F620000000000000002000001471502000900000000FA0133001600000100000014000000020000000400000008000000180000000D00000001000000010000000300000000000A000100000000000600380031000A0060004B000A006B004B000600B900A7000600F100D20006000501A70006001E01A70006003901A70006005401A70006006D01A70006008601A7000600A501A7000600C201A7000600F901D90106001902D9010A00520237020E009E027F020E00A4027F020E00AA027F020A00E10237020000000001000000000001000100010010001B000000050001000100502000000000960075000A0001007020000000009600800013000300C3200000000096008D0013000600E420000000008618A1001E000900000001006702000002006D02000001006702000002006D0200000300C802000001006702000002006D0200000300C8022100A10022002900A10027003100A10022003900A10022004100A10022004900A10022005100A10022005900A10022006100A10022006900A10022007100A1002C007900A1001E008100A1001E0019007502D5008900A402D9009900B002E0001100BC02E4008900A10022008900A402EA008900D402F0001900BC02F7001900DC02FD00A100A1001E000900A1001E0020006B0031002E0033007F022E003B0079022E0063009A022E00230079022E002B0079022E00430079022E00130079022E004B0079022E0053007F022E005B00910240006B0031006000BB00060101010480000001000000BE0B28760000000000001B000000020000000000000000000000010028000000000002000000000000000000000001003F0000000000020000000000000000000000010031000000000000000000003C4D6F64756C653E0052656765785574696C6974792E646C6C0052656765785574696C697479006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E670052656765784D617463680052656765785265706C6163650052656765785265706C61636544656661756C74002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500696E707574007061747465726E006765745F56616C75650053797374656D2E546578742E526567756C617245787072657373696F6E73005265676578004D617463680047726F7570006765745F53756363657373006F705F496D706C69636974007265706C6163656D656E74005265706C616365004E756C6C0053716C4D6574686F64417474726962757465000003200000000000253F930E81E4D84AAC7FD0EB9C3404A30008B77A5C561934E0890800021109110D110D0A0003110D110D110D110D03200001042001010E0420010102042001010880A20100020054020F497344657465726D696E6973746963015455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A44617461416363657373000000000320000E06000212490E0E0320000205000111090205200112490E0600030E0E0E0E050001110D0E0306110D040701124581710100060054020F497344657465726D696E6973746963015402094973507265636973650154020A4F6E4E756C6C43616C6C005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000540E044E616D651572656765785F7265706C6163655F64656661756C740501000000001101000C52656765785574696C69747900000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000C42900000000000000000000DE290000002000000000000000000000000000000000000000000000D0290000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000E00200000000000000000000E00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001002876BE0B000001002876BE0B3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00440020000010053007400720069006E006700460069006C00650049006E0066006F0000001C020000010030003000300030003000340062003000000044000D000100460069006C0065004400650073006300720069007000740069006F006E0000000000520065006700650078005500740069006C006900740079000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003000300036002E00330030003200340038000000000044001100010049006E007400650072006E0061006C004E0061006D0065000000520065006700650078005500740069006C006900740079002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004C00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000520065006700650078005500740069006C006900740079002E0064006C006C00000000003C000D000100500072006F0064007500630074004E0061006D00650000000000520065006700650078005500740069006C006900740079000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003000300036002E00330030003200340038000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003000300036002E0033003000320034003800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000F03900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE

GO
  1. 创建 Assembly

    –3. 创建 CLR 函数
    CREATE FUNCTION [dbo].regex_replace, @pattern nvarchar, @replacement nvarchar)
    RETURNS nvarchar WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
    AS
    EXTERNAL NAME [RegexUtility].[RegexUtility].[RegexReplaceDefault]
    GO

    –4. 使用regex_replace替换多个空格为2个空格
    select dbo.regex_replace(‘agc f f ‘,’\s+’,’ ‘);

  1. 创建 Assembly

    –3. 创建 CLR 函数
    CREATE FUNCTION [dbo].regex_replace, @pattern nvarchar, @replacement nvarchar)
    RETURNS nvarchar 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+’,’ ‘);

注:通过CL福特Explorer达成更加多REGEXP函数,假诺有尖端语言开采手艺,可以自动开辟;只怕直接利用一些开源进献也行,举例:

注:通过CL中华V完成更加多REGEXP函数,假诺有高等语言开荒技巧,能够自动开垦;大概直接利用一些开源进献也行,比如:

 

 

小结:

小结:

  1. 非正则SQL语句的思绪,对两样数据库往往都适用;

  2. 正则表达式中的规则(pattern)
    在分化开采语言里,有为数不少语法是相通的,平日是服从perl恐怕linux
    shell中的sed等工具的条条框框;

  3. 从质量上来看,通用SQL决断 > REGEXP函数 > 自定义SQL函数。

  1. 非正则SQL语句的笔触,对两样数据库往往都适用;

  2. 正则表明式中的规则(pattern)
    在不相同开辟语言里,有为数不少语法是相通的,平常是信守perl大概linux
    shell中的sed等工具的条条框框;

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

 

 

参考:

参考:

 

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website