Shulou(Shulou.com)06/02 Report--

This article introduces you how to use regular expressions in Oracle, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Here are some examples to illustrate the use of regular expressions to deal with common problems in your work.

1.

REGEXP_SUBSTR

The REGEXP_SUBSTR function uses regular expressions to specify the start and end of the return string, returning the same string as the VARCHAR2 or CLOB data in the source_string character set.

Syntax:

-- 1.REGEXP_SUBSTR is the same as the SUBSTR function and returns the truncated substring

REGEXP_SUBSTR (srcstr, pattern [, position [, occurrence [, match_option])

Note:

Srcstr

Source string

Pattern

Regular expression style

Position

Start matching character position

Occurrence

Number of matching occurrences

Match_option

Matching options (case sensitive)

1.1 truncate a substring from a string

SELECT regexp_substr ('1PSNamp) 231pm / 3253 / ABCs,' [[: alnum:]] +') FROM dual

Output: 1PSN

[[: alnum:]] + means to match one or more alphanumeric characters.

SELECT regexp_substr ('1p SNamp, 231pm, 3253) FROM dual,'[[: alnum:]] +', 1,2)

Output: 231

Compared with the above example, there are two more parameters

one

Means to find a match starting from the first character of the source string

two

Represents the string matched for the second time (the default value is "1", as in the example above)

Select regexp_substr ('@ @ / 231 March 3253 Universe ABC) * [[: alnum:]] +') from dual

Output: 231

@ * means to match 0 or more @

[[: alnum:]] + indicates matching one or more alphanumeric characters

Note: you need to pay attention to the difference between "+" and "*"

Select regexp_substr ('1times alnum / 3253 / 231 / 3253 / 3253 / 231 / 3253 / 3253 / 3253 / 231 / 3253 / 3253 / 3253 / 231 / 3253 / 3253 / 231 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 3253 / 325

Output: @

@ + means to match one or more @

[[: alnum:]] * indicates matching 0 or more alphanumeric characters

Select regexp_substr ('1x alnum:] +') from dual ('1x: 3253)

Output: Null

@ + means to match one or more @

[[: alnum:]] + indicates matching one or more alphanumeric characters

Select regexp_substr ('@ 1p SNamp 231pm 3253Universe ABc125mm select regexp_substr'[[: digit:]] + $') from dual

Output: 125

[[: digit:]] + $indicates a character that matches the end of one or more numbers

Select regexp_substr ('@ 1p SNamp 231pm 3253 Universe ABC) from dual'[^ [: digit:]] + $')

Output: / ABc

[^ [: digit:]] + $means to match one or more characters that do not end with a number

Select regexp_substr ('Tom_Kyte@oracle.com',' [^ @] +') from dual

Output: Tom_Kyte

[^ @] + matches one or more characters that are not "@"

Select regexp_substr ('1PSNUniverse 231pm 3253Universe ABCLERIME' [[: alnum:]] *', 1JEI 2)

From dual

Output: Null

[[: alnum:]] * indicates matching 0 or more alphanumeric characters

Note: because there are 0 or more matches, the second match here is "/" (0 matches), not "231", so the result is "Null"

1.2 matching recurrence

Find 2 consecutive lowercase letters

SELECT regexp_substr ('Republicc Of Africaa',' ([a murz])\ 1, 1, 1,'i')

FROM dual

Output: cc

([aMuz])

Denotes the lowercase letter amerz

\ 1

Represents the number of consecutive times that match the preceding characters

one

Indicates that the match starts with the first character of the source string

one

The characters that match the matching result appear for the first time

I

Indicates case sensitivity

1.3 some other matching styles

Find web address information

SELECT regexp_substr ('Go to http://www.oracle.com/products and click on database', 'http://([[:alnum:]]+\.?){3,4}/?') RESULT

FROM dual

Output: http://www.oracle.com

Where:

Http://

Represents the matching string "http://""

([[: alnum:]] +\.?) Indicates that one or more alphanumeric characters are matched, followed by 0 or 1 comma

{3,4}

Indicates that the preceding characters are matched at least 3 times and 4 times at most

/?

Indicates that a backslash character is matched 0 or 1 times

Extract the third value from the csv string

SELECT regexp_substr ('1101, Yokohama, Japanese, 1.5.105, [^,] +, 1, 3) AS output

FROM dual

Output: Japan

Where:

[^,] +

Means to match one or more characters that are not commas

one

Means to find a match starting from the first character of the source string

three

Represents the string matched for the third time

Note: this is usually used to implement the column line of a string.

-- the column line of the string

SELECT regexp_substr ('1101, Yokohama, Japanese, 1.5.105, [^,] +, 1, LEVEL) AS output

FROM dual

CONNECT BY LEVEL

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.