Regular expressions in SQL statements

Regular Expressions

Regexp_like performing Regular expression matching

SELECT first_name

From EMPLOYEES

WHERE regexp_like (first_name, ' ^al (An|yss) a$ ', ' I ');--al is followed by an OR Yss

SELECT first_name

From EMPLOYEES

WHERE regexp_like (first_name, ' ^al (.) {2}a$ ', ' I ');--any one of the letters appearing two times in the middle

SELECT first_name

From EMPLOYEES

WHERE regexp_like (first_name, ' ^al[^y]+a$ ', ' I ');--The third letter does not allow Y to always match

Regexp_replace performing Regular expression substitution

SELECT phone_number,regexp_replace (Phone_number, ' \. ', '-') as PHONE from employees;--must be added with escape characters \ Not added '. ' Represents any character

Regexp_instr where the search appears

SELECT street_address,

Regexp_instr (Street_address, ' [[: Alpha:]] ') as first_alpha_position,--where any letter first appears

Regexp_instr (Street_address, ' [[:d igit:] ') as first_alpha_position,--where any number first appears

Regexp_instr (Street_address, ' [[: Alnum:] ') as first_alpha_position--where any number or letter appears for the first time

From LOCATIONS;

Using REGEXP_SUBSTR to control character output

SELECT street_address,

Regexp_substr (street_address, ' [^]+ ') as ROAD1,

Regexp_substr (street_address, ' [^]+ ') as ROAD2,

Regexp_substr (street_address, ' [^]+ ') as ROAD3,

Regexp_substr (street_address, ' [^]+ (.) + ') as ROAD4,

Regexp_substr (Street_address, ' [[: Alpha:]] ') as ROAD5,

Regexp_substr (Street_address, ' [[: alpha:]]+ ') as ROAD6,

Regexp_substr (Street_address, ' [[: alpha:]]+ (.) ') As ROAD7,

Regexp_substr (Street_address, ' [[: alpha:]]+ (.) + ') as ROAD8

From LOCATIONS;

. Matches any character in the supported character set, except the empty

+ Match Previous sub-expression one or more times

| Specify an option within two items

' I ' case-insensitive matching

[[: Alpha:]] any letter

[[:d Igit:]] any number

[[: Alnum:]] Any letters and numbers

[[: Space:]] any space

Sub-expression

SELECT

Regexp_instr

(' 0123456789 ',--source character

' (123) (4 (+)) ',--expression used for matching

1,--where to start the search

1,--on the first couple of matches

0,--0 returns to the first occurrence position; 1 Next start position

' I ',--case insensitive

1) RESULT--the first few expressions

From DUAL;

An expression with parentheses in the subexpression

In (123 (4 (56) (78)) (123 (4 (56) (78)) are the first sub-expressions

(4 (56) (78)) The second expression (56) is the third (78) is the fourth

CHECK constraints and regular expressions: an example

ALTER TABLE EMP8

ADD CONSTRAINT email_addr

CHECK (regexp_like (email, ' @ ')) novalidate;

INSERT into EMP8 VALUES

($, ' Christian ', ' patal ', ' chrisp2creme.com ',

1234567890, ' 12-jan-2004 ', ' Hr-rep ', 2000,null,102,40);

SELECT * from T50 where regexp_like (email, ' [^@][@][[:alnum:]]+ ([\.] [[: Alnum:]]+| [\.] [[: Alnum:]]+[\.] [[: alnum:]]+] [^\.] $ ');

ALTER TABLE t50 ADD CONSTRAINT email_addr CHECK (regexp_like (email, ' [^@][@][[:alnum:]]+ ([\.] [[: Alnum:]]+| [\.] [[: Alnum:]]+[\.] [[: alnum:]]+] [^\.] $ ')) Novalidate;

Table 1: Positional metacharacters

Metacharacters
Description

^
To position an expression at the beginning of a line

$
To position an expression at the end of a line

Table 2: quantifiers or repeating operators

Quantifiers
Description

*
Match 0 or more times

?
Match 0 or 1 times

+
Match 1 or more times

{m}
Exactly match M-Times

{m,}
Matches at least m times

{m, n}
Matches at least m times but not more than n times

Table 3: Pre-defined POSIX character classes

Character class
Description

[: Alpha:]
Alphabetic characters

[: Lower:]
Lowercase alphabetic characters

[: Upper:]
Uppercase characters

[:d Igit:]
Digital

[: Alnum:]
Alpha-Numeric characters

[: Space:]
White space characters (prohibit printing), such as carriage returns, line feeds, vertical tabs, and page breaks

[:p UNCT:]
Punctuation character

[: Cntrl:]
control characters (prohibit printing)

[:p rint:]
printable characters

Table 4: Substitution matching and grouping of expressions

Metacharacters
Description

|
Replace
Delimited substitution options, typically used with the grouping operator ()

( )
Group
Grouping sub-expressions into a replacement unit, quantifier cell, or back reference unit (see Back Reference section)

[Char]
Character List
Represents a word list characters; most metacharacters (except character classes, ^ and-metacharacters) in a character list are understood as literals

Table 5:regexp_like Operators

Grammar
Description

Regexp_like (source_string, pattern
[, Match_parameter])
Source_string supports character data types (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB, but does not include LONG). The pattern parameter is another name for the regular expression. Match_parameter allows optional parameters such as handling line breaks, preserving multiline formatting, and providing case-sensitive control.

Table 6:regexp_instr Functions

Grammar
Description

Regexp_instr (source_string, pattern
[, Start_position
[, Occurrence
[, Return_option
[, Match_parameter]]])
The function looks for pattern and returns the first position of the pattern. Feel free to specify the start_position you want to start searching for. The occurrence parameter defaults to 1 unless you specify that you want to find a pattern that appears next. The default value of Return_option is 0, which returns the starting position of the pattern, and a value of 1 returns the starting position of the next character that matches the matching criteria.

Table 7: Description of 5-digit plus 4-bit postal code expression

Grammar
Description

Whitespace that must be matched

[:d Igit:]
POSIX Number Class

]
End of character list

{5}
The word list characters just repeats 5 times.

(
The beginning of a sub-expression

-
A literal hyphen, because it is not a character list within the range metacharacters

[
The beginning of the character list

[:d Igit:]
POSIX [:d igit:] Class

[
The beginning of the character list

]
End of character list

{4}
The word list characters just repeats 4 times.

)
End parenthesis, end subexpression

?
? Quantifiers Match sub-expressions grouped by 0 or 1 times, making the 4-bit code optional

$
Positional metacharacters, indicating line endings

Table 8:regexp_substr Functions

Grammar
Description

Regexp_substr (source_string, pattern
[, position [, occurrence
[, Match_parameter]])
The REGEXP_SUBSTR function returns a substring of the matching pattern.

Table 9:regexp_replace Functions

Grammar
Description

Regexp_replace (source_string, pattern
[, replace_string [, Position
[, occurrence, [match_parameter]]]
The function replaces the matching pattern with a specified replace_string, allowing for complex "search and replace" operations.

Table 10: Back-referencing meta-characters

Metacharacters
Description

\digit
Reverse Slash
followed by a number between 1 and 9, the backslash matches the preceding digit subexpression, enclosed in parentheses.
(Note: Backslashes have another meaning in the regular expression, depending on the context, it may also represent the Escape character.)

Table 11: Description of the pattern Exchange regular expression

Regular expression Items
Description

(
The beginning of the first sub-expression

.
Match any single character except a newline character

*
Repeat operator to match the previous. Meta-characters 0 to N times

)
The end of the first subexpression; match results in \1
(In this case, the result is Ellen. ）

Whitespace that must exist

(
The beginning of the second sub-expression

.
Match any single character except for a line break

*
Repeat operator to match the previous. Meta-characters 0 to N times

)
The end of the second subexpression; match results in \2
(In this case, the result is hildi. ）

Blank

(
The beginning of the third sub-expression

.
Match any single character except a newline character

*
Repeat operator to match the previous. Meta-characters 0 to N times

)
The end of the third subexpression; match results in \3
(In this case, the result is Smith.) ）

Table 12: Description of regular expressions for social Security numbers

Regular expression Items
Description

^
The beginning of the line character (the regular expression cannot have any leading characters before the match.) ）

(
Start subexpression and list with | Replaceable options for meta-character separation

[
The beginning of the character list

[:d Igit:]
POSIX Number Class

]
End of character list

{3}
The word list characters just repeats 3 times.

-
Hyphens

[
The beginning of the character list

[:d Igit:]
POSIX Number Class

]
End of character list

{2}
The word list characters just repeats 2 times.

-
Another hyphen

[
The beginning of the character list

[:d Igit:]
POSIX Number Class

]
End of character list

{4}
The word list characters just repeats 4 times.

|
Replace meta-character; end first option and start next substitution expression

[
The beginning of the character list

[:d Igit:]
POSIX Number Class

]
End of character list

{9}
The word list characters just repeats 9 times.

)
End parenthesis, end subexpression Group for substitution

$
Positional metacharacters, indicating the end of the line; no extra characters to match the pattern

Regular expressions in SQL statements