Oracle 10g Regular Expressions

Oracle finally provides support for regular expressions in 10G, where complex matches that need to be done by like can be simpler to implement using regular expressions.

There are four main functions in Oracle that support regular expressions:

1,regexp_like: Similar to like function

2,REGEXP_INSTR: Similar to INSTR function

3,REGEXP_SUBSTR: Similar to SUBSTR function

4,regexp_replace: Similar to REPLACE function

Use regular expressions in new functions instead of wildcard '% ' and ' _ '.

Regular expressions are composed of standard metacharacters (metacharacters):

' ^ ' matches the start position of the input string, used in a bracket expression, which indicates that the character set is not accepted.

' $ ' matches the end position of the input string. If the Multiline property of the RegExp object is set, then $ also matches ' n ' or ' R '.

'. ' matches any single character except the newline character N.

'? ' matches the preceding subexpression 0 times or once.

' + ' matches the preceding subexpression one or more times.

' * ' matches the preceding subexpression 0 or more times.

' | ' indicates a choice between the two items. Example ' ^ ([a-z]+|[ 0-9]+) $ ' represents a string of all lowercase letters or numbers combined.

' () ' marks the beginning and end position of a subexpression.

' [] ' marks a bracket expression.

' {m,n} ' an exact number of occurrences,m=< occurrences <=n, ' {m} ' indicates that M times, ' {m,} ' indicates that at least m times appear.

num matches num, where num is a positive integer. A reference to the match that was obtained.

Character clusters:

[[: Alpha:]] any letter.

[[:d Igit:]] any number.

[[: Alnum:]] any letter or number.

[[: Space:]] any white character.

[[: Upper:]] any uppercase letter.

[[: Lower:]] any lowercase letter.

[[:p UNCT:]] any punctuation.

[[: Xdigit:]] Any number in 16, equivalent to [0-9a-fa-f].

Operation Precedence for various operators

Escape character

(), (?:), (? =), [] parentheses and square brackets

*, +,?, {n}, {n,}, {n,m} qualifier

^, $, anymetacharacter position and order

| "or" action

Here are a few examples to illustrate how these new functions are used:

Sql> CREATE TABLE Sunwg (ID varchar2 (100));

Table created.

sql> INSERT into SUNWG values (' <a href= ' http://sunwgneuqsoft.itpub.net/post/34741/447698 ' > How common SQL Accesses indexes < /a> ');

1 row created.

Sql> commit;

Commit complete.

Sql> select * from Sunwg;

Id

----------------------------------------------------------------------------------------------------

<a href= "http://sunwgneuqsoft.itpub.net/post/34741/447698" > How common SQL Access indexes </a>

1, Regexp_like

Regexp_like and like, most of the operations that can be done with regexp_like can be done with like, but much simpler and more convenient.

<a> Objectives: Query table Sunwg for similar and 3xx41 records.

Like:

SELECT * from Sunwg where id like '%3__41% ';

Regexp_like

SELECT * from Sunwg where regexp_like (ID, ' 3..41 ');

<b> Objectives: Query table Sunwg for similar and 3xx41 records, and XX must be a number.

Like:

This like I want to come up with a good way to achieve, the only thought is to intercept the string is not a pure figure.

Regexp_like

SELECT * from Sunwg where regexp_like (ID, ' 3[0-9]{2}41 ');

With Regexp_like, the result can be obtained easily and quickly. Several other functions also have similar conditions, the following functions are not specific differences, just give a common usage.

2, Regexp_instr

<a> Objective: To query table Sunwg for the presence of a string similar to the 3xx41 in the first occurrence.

Sql> Select Regexp_instr (ID, ' 3..41 ', 1,1) from Sunwg;

Regexp_instr (ID, ' 3..41 ', 1, 1)

----------------------------

46

Sql> Select substr (id,46,5) from Sunwg;

SUBST

-----

34741

3, Regexp_substr

<a> goal: Intercepts the URL address in the table Sunwg.

Sql> Select Regexp_substr (ID, ' http[0-9a-za-z/:.] + ') from Sunwg;

Regexp_substr (ID, ' http[0-9a-za-z/:.] +')

----------------------------------------------------------------------------------------------------

http://sunwgneuqsoft.itpub.net/post/34741/447698

4, Regexp_replace

<a> goal: Replace the URL in the table Sunwg with the address of www.163.com?

Sql> Select Regexp_replace (ID, ' http[0-9a-za-z/:.] + ', ' www.163.com ') from Sunwg;

Regexp_replace (ID, ' http[0-9a-za-z/:.] + ', ' WWW.163.COM ')

--------------------------------------------------------------------------------------------------------------- ---------------------------------------

<a href= "www.163.com" > How common SQL Access indexes </a>

As you can see from the example above, these functions that support regular expressions are very powerful, and using them properly will certainly make the SQL you write more simple and efficient.

Regexp_substr

Regexp_substr (string, pattern, position) Regexp_substr (string, pattern, position, occurence) Regexp_substr (string, pattern, position, occurence, parameters)

Parameters can be a combination of regexp_substr (string, pattern) I: to match case Inse nsitively C: To match, sensitively N: To make the dot (.) match new lines as OK m: to Mak e ^ and $ match beginning and end of a line in a multiline string regexp_substr are an Oracle SQL function that enables Reg ular expressions in queries. It enhances the ' traditional ' substr. Links Also on splitting a string into the words with regular expressions where a function uses Regexp_substr to split a string. Then There is also Safe_to_number () where Regexp_substr was used to convert strings to numbers.

Regexp_instr Regexp_instr(String, pattern) Regexp_instr(string, pattern, position) Regexp_instr(string, pattern, position, occurence) Regexp_instr(string, pattern, position, occurence, return-option) Regexp_instr(string, pattern, position, occurence, return-option, parameters) ParametersParameters can be a combination of I: to match case insensitively C: to match case sensitively N: To make the dot (.) match new lines as OK m: To do ^ and $ match beginning and end of the a line in a multiline string x: to ignore white spaces. Regexp_instr is a Oracle SQL function that enables regular expressions in queries. It enhances the ' traditional ' instr.

Regexp_like Regexp_like(string, pattern); Regexp_like(string, pattern, parameters); Parameters can be a combination of I: to match case insensitively C: to match case sensitively N: To make the dot (.) match new lines as OK m: To do ^ and $ match beginning and end of a-a multiline string regexp_like is-a Oracle SQL function that enabl ES regular expressions in queries. It enhances the«traditional»like. Regexp_like is a pattern condition. Demonstration

Strings (
  str  varchar2)
;

 Patterns (
  Pat  varchar2 (),
  DSC  Varchar2 ()
);

INSERT into patterns values (' ^[[:d igit:]]{3}-[[:d igit:]]{2}-[[:d igit:]]{4}

INSERT into strings values (' 987-65-4321 ');
INSERT into strings values (' Hello foo bar ');
INSERT into strings values (' 4987-65-4321 ');
INSERT into strings values (' Hello FOO BAR ');
INSERT into strings values (' -4.55 ');
INSERT into strings values (' 987-65-43213 ');
INSERT into strings values (' 4.55 ');
INSERT into strings values (' Hello bar bar ');
INSERT into strings values (' 4.55 ');
INSERT into strings values (' 1234567890 ');
INSERT into strings values (' Hello foo foo ');

  STR,DSC 
  from strings  patterns
where
  regexp_like(str, PAT);

STR-                            DSC
------------------------------------------------------------
987-65-4321                    Social Security Number
Hello bar bar                  repeated words
Hello foo foo                  repeated words
Hello foo bar                  only lowercase words
Hello bar bar only                  lowercase words
1234567890 only digits
987-65-4321                    At least one digit
4987-65-4321 in                   least one digit
-4.55 at least one                          digit
987-65-43213                   At least one digit
4.55 in                           least one digit
 4.55 at least one                          digit
1234567890 at least one                     D Igit
-4.55                          number
4.55                           number
1234567890                     number

Regexp_replace Regexp_replace(String, pattern) Regexp_replace(String, pattern, replace-string) Regexp_replace(String, pattern, replace-string, position) Regexp_replace(String, pattern, replace-string, position, occurence) Regexp_replace(String, pattern, replace-string, position, occurence, parameters) parameters can be a combination of I: to match case insensitively C: to match case sensitively N: To make the dot (.) match new lines as OK m: To do ^ and $ match beginning and end of a-a multiline string regexp_substr is-a Oracle SQL function that Ena Bles regular expressions in queries. It enhances the ' traditional ' substr. Regexp_replace is a Oracle SQL function that enables regular expressions in queries. It enhances the ' traditional ' replace. Demonstration

Strings (
  str varchar2)
;

 Patterns (
  Pat  varchar2,
  repl varchar2 (),
  DSC  Varchar2 ()
);

INSERT into patterns values (' ^[[:space:]]*[^[:space:]]+[[:space:]]+ ([^[:space:]]+). * ', '/1 ', ' the 2nd word ');
INSERT into patterns values (' ^[^[:d igit:]]* ([[:d igit:]]*/.? [[:d igit:]]+]. * '          , '/1 ', ' 1st number ');
INSERT into patterns values (' ^[^[:upper:]]* ([: upper:]]+). * '                         , '/1 ', ' uppercase word ');

INSERT into strings values (' foo  bar   Baz ');
INSERT into strings values (' Bla   MOO   82.22    7.34  bla ');
INSERT into strings values ('  one   two 3 four  ');

Found format A20

select
  str,
  regexp_replace(str, Pat, REPL) found,
  DSC
from
  strings  Patterns
where
  (Str,pat) > 0;

STR                            FOUND                DSC
----------------------------------------------------------------------
foo  Bar   Baz                 bar the                  2nd word
bla   moo   82.22    7.34  bla moo the                  2nd Word
  one   two 3 four             two                  2nd word
bla   MOO   82.22    7.34                bla 82.22 the 1st number
  One   two 3 four             3 the                    1st number
bla   moo   82.22    7.34  bla Moo                  Uppercase Word

LinksAlso on using Regexp_replace to format data. , ' Social security Number '); INSERT into patterns values (' [^[:alpha:]] ([[: alpha:]]+) [^[:alpha:]] */1 ', ' repeated words '); INSERT into patterns values (' ^ ([: lower:]]|) *

___fckpd___5

___fckpd___6

___fckpd___7

___fckpd___8

Parameters can be a combination of I: to match case insensitively C: to match case sensitively N: To make the dot (.) match new lines as OK m: To do ^ and $ match beginning and end of a-a multiline string regexp_substr is-a Oracle SQL function that Ena Bles regular expressions in queries. It enhances the ' traditional ' substr. Regexp_replace is a Oracle SQL function that enables regular expressions in queries. It enhances the ' traditional ' replace