What is a regular expression

A regular expression is similar to a rule which defines the characters that can appear in an expression. In SQL databases, selecting field values based on regular expressions can be very useful. One example would be to validate credit card numbers or telephone numbers from a given database field. Another would be to check numeric values matching a particular sequence, such as integers containing the digits 99 or 88. SQL server database implementations provide built-in regular expression support.

 

Special Characters and Constructs

The following table provides a summary of the more commonly used special characters and constructs.

 

Named Characters Table (English)

The following table describes the standard character names that can be used within regular expression bracket pairs ([[.char]] – see above). Character names are location specific, so non-English versions of Guardium may use a different set of character names.

Name	Value
NUL	\0
SOH	\001
STX	\002
ETX	\003
EOT	\004
ENQ	\005
ACK	\006
BEL	\007
alert	\007
BS	\010
backspace	\b
HT	\011
tab	\t
LF	\012
newline	\n
VT	\013
vertical-tab	\v
FF	\014
form-feed	\f
CR	\015
carriage-return	\r
SO	\016
SI	\017
DLE	\020
DC1	\021
DC2	\022
DC3	\023
DC4	\024
NAK	\025
SYN	\026
ETB	\027
CAN	\030
EM	\031
SUB	\032
ESC	\033
IS4	\034
FS	\034
IS3	\035
GS	\035
IS2	\036
RS	\036
IS1	\037
US	\037
space	' '
exclamation-mark	!
quotation-mark	"
number-sign	#
dollar-sign	$
percent-sign	%
ampersand	&
apostrophe	\'
left-parenthesis	(
right-parenthesis	)
asterisk	*
plus-sign
comma	,
hyphen
hyphen-minus
period	.
full-stop	.
slash	/
solidus	/
zero	0
one	1
two	2
three	3
four	4
five	5
six	6
seven	7
eight	8
nine	9
colon	:
semicolon	;
less-than-sign	<
equals-sign
greater-than-sign	>
question-mark	?
commercial-at	@
left-square-bracket	[
backslash	\\
reverse-solidus	\\
right-square-bracket	]
circumflex	^
circumflex-accent	^
underscore
low-line
grave-accent	`
left-brace	{
left-curly-bracket	{
vertical-line	|
right-brace	}
right-curly-bracket	}
tilde	~
DEL	177
NULL	0

Regular Expression Examples

You can copy and paste any of the expressions from the right-hand column to a field requiring a regular expression. When using any of these examples, we strongly suggest that you experiment by using it in the Build Regular Expression tool, entering a variety of matching and non-matching values, so that you understand exactly what is being matched by the expression.

Description	Regular Expressions
Social Security Number (must have hyphens)	[0-9]{3}-[0-9]{2}-[0-9]{4}
Phone Number	\(?[0-9]{3}\)?[-. ]?[0-9]{3}[-. ]?[0-9]{4}
(North America - Matches 3334445555,
333.444.5555,
333-444-5555,
333 444 5555,
(333) 444 5555,
and all combinations thereof)
Postal Code - (Canada)	[ABCEGHJKLMNPRSTVXY][0-9][A-Z] [0-9][A-Z][0-9]
Postal Code - (UK)	[A-Z]{1,2}[0-9][A-Z0-9]? [0-9][ABD-HJLNP-UW-Z]{2}
Zip Code (US)	[0-9]{5}(?:-[0-9]{4})?
(5 digits required, hyphen followed by four digits optional)l
Credit Card Numbers	[0-9]{4}[-, ]?[0-9]{4}[-, ]?[0-9]{4}[-, ]?[0-9]{4}

 

REGEXP_COUNT

The REGEXP_COUNT function returns a count of the number of times that a regular expression pattern is matched in a string.

Example

• Count the number of times “Steven” or “Stephen” occurs in the string “Steven Jones and Stephen Smith are the best players”.

  SELECT REGEXP_COUNT( 
    'Steven Jones and Stephen Smith are the best players',
    'Ste(v|ph)en')
  FROM sysibm.sysdummy1

  The result is 2.

REGEXP_SUBSTR

The REGEXP_SUBSTR function returns one occurrence of a substring of a string that matches the regular expression pattern.

Examples

• Example 1: Return the string which matches any character preceding a ‘o’.

  SELECT REGEXP_SUBSTR('hello to you', '.o',1,1) 
  FROM sysibm.sysdummy1

  The result is ‘lo’.

• Example 2: Return the second string occurrence which matches any character preceding a ‘o’.

  SELECT REGEXP_SUBSTR('hello to you', '.o',1,2) 
  FROM sysibm.sysdummy1

  The result is ‘to’.

• Example 3: Return the third string occurrence which matches any character preceding a ‘o’.

  SELECT REGEXP_SUBSTR('hello to you', '.o',1,3) 
  FROM sysibm.sysdummy1

  The result is ‘yo’.

REGEXP_INSTR

The REGEXP_INSTR returns the starting position or the position after the end of the matched substring, depending on the value of the return_option argument.

Examples

• Example 1: Find the first occurrence of a ‘o’ which has a character preceding it.

  SELECT REGEXP_INSTR('hello to you', '.o',1,1) 
  FROM sysibm.sysdummy1

  The result is 4, which is the position of the second ‘l’ character.

• Example 2: Find the second occurrence of a ‘o’ which has a character preceding it.

  SELECT REGEXP_INSTR('hello to you', '.o',1,2) 
  FROM sysibm.sysdummy1

  The result is 7, which is the position of the character ‘t’.

• Example 3: Find the position after the third occurrence of the first capture group of the regular expression ‘(.o).’ using case insensitive matching.

  SELECT REGEXP_INSTR('hello to you', '(.o).', 1,3,1,'i',1) 
  FROM sysibm.sysdummy1

  The result is 12, which is the position of the character ‘u’ at the end of the string.

REGEXP_REPLACE

The REGEXP_REPLACE function returns a modified version of the source string where occurrences of the regular expression pattern found in the source string are replaced with the specified replacement string.

 

Example

• Replace the second occurrence of the pattern ‘R.d’ with ‘Orange’ using a case sensitive search.

  SELECT REGEXP_REPLACE(
    'Red Yellow RED Blue Red Green Blue',
    'R.d','Orange',1,2,'c') 
  FROM sysibm.sysdummy1

  The result is ‘Red Yellow RED Blue Orange Green Blue’.

REGEXP_LIKE

The REGEXP_LIKE predicate searches for a regular expression pattern in a string.

Regular expression flag values

Flag value	Description
c	Specifies that matching is case sensitive. This is the default value if neither 'c' nor 'i' is specified. This value must not be specified with a value of 'i'.
i	Specifies that matching is case insensitive. This value must not be specified with a value of 'c'.
m	Specifies that the input data could contain more than one line. By default, the '^' and the '$' in a pattern will only match the start and the end, respectively, of the input string. If this flag is set, "^" and "$" will also match at the start and end of each line within the input string.
n	Specifies that the '.' character in a pattern matches a line terminator in the input string. By default, the '.' in a pattern will not match a line terminator. A carriage-return and line-feed pair in the input string behaves as a single line terminator, and will match a single "." in a pattern.
s	Specifies that the '.' character in a pattern matches a line terminator in the input string. This is a synonym for the 'n' value.
x	Specifies that white space characters in a pattern are ignored, unless escaped.

Regular Expression Metacharacters

Character	Outside of sets	[Inside sets]	Description
\a	✔	✔	Match a BELL, \u0007
\A	✔		Match at the beginning of the input. Differs from ^ in that \A will not match after a new line within the input.
\b	✔		Match if the current position is a word boundary. Boundaries occur at the transitions between word (\w) and non-word (\W) characters, with combining marks ignored.
\B	✔		Match if the current position is not a word boundary.
\cX	✔	✔	Match a control-X character.
\d	✔	✔	Match any character with the Unicode General Category of Nd (Number, Decimal Digit.)
\D	✔	✔	Match any character that is not a decimal digit.
\e	✔	✔	Match an ESCAPE, \u001B.
\E	✔	✔	Terminates a \Q ... \E quoted sequence.
\f	✔	✔	Match a FORM FEED, \u000C.
\G	✔		Match if the current position is at the end of the previous match.
\n	✔	✔	Match a LINE FEED, \u000A.
\N{UNICODE CHARACTER NAME}	✔	✔	Match the named character.
\p{UNICODE PROPERTY NAME}	✔	✔	Match any character with the specified Unicode Property.
\P{UNICODE PROPERTY NAME}	✔	✔	Match any character not having the specified Unicode Property.
\Q	✔	✔	Quotes all following characters until \E.
\r	✔	✔	Match a CARRIAGE RETURN, \u000D
\s	✔	✔	Match a white space character. White space is defined as [\t\n\f\r\p{Z}].
\S	✔	✔	Match a non-white space character.
\t	✔	✔	Match a HORIZONTAL TABULATION, \u0009.
\uhhhh	✔	✔	Match the character with the hex value hhhh.
\Uhhhhhhhh	✔	✔	Match the character with the hex value hhhhhhhh. Exactly eight hex digits must be provided, even though the largest Unicode code point is \U0010ffff.
\w	✔	✔	Match a word character. Word characters are
			[\p{Alphabetic}\p{Mark}\p{Decimal_Number}
			\p{Connector_Punctuation}\u200c\u200d].
\W	✔	✔	Match a non-word character.
\x{hhhh}	✔	✔	Match the character with hex value hhhh. From one to six hex digits may be supplied.
\xhh	✔	✔	Match the character with two digit hex value hh
\X	✔		Match a Grapheme Cluster
\Z	✔		Match if the current position is at the end of input, but before the final line terminator, if one exists.
\z	✔		Match if the current position is at the end of input.
\n	✔		Back Reference. Match whatever the nth capturing group matched. n must be a number > 1 and < total number of capture groups in the pattern.
\0ooo	✔	✔	Match an Octal character. 'ooo' is from one to three octal digits. 0377 is the largest allowed Octal character. The leading zero is required; it distinguishes Octal constants from back references.
[pattern]	✔	✔	Match any one character from the set.
.	✔		Match any character.
^	✔		Match at the beginning of a line.
$	✔		Match at the end of a line.
\	✔		Quotes the following character. Characters that must be quoted to be treated as literals are * ? + [ ( ) { } ^ $ | \ .
\		✔	Quotes the following character. Characters that must be quoted to be treated as literals are [ ] \ Characters that may need to be quoted, depending on the context are - &

Regular Expression Operators

Operator	Description
|	Alternation. A|B matches either A or B.
*	Match 0 or more times. Match as many times as possible.
+	Match 1 or more times. Match as many times as possible.
?	Match zero or one times. Prefer one
{n}	Match exactly n times
{n,}	Match at least n times. Match as many times as possible.
{n,m}	Match between n and m times. Match as many times as possible, but not more than m.
*?	Match 0 or more times. Match as few times as possible.
+?	Match 1 or more times. Match as few times as possible.
??	Match zero or one times. Prefer zero.
{n}?	Match exactly n times
{n,}?	Match at least n times, but no more than required for an overall pattern match
{n,m}?	Match between n and m times. Match as few times as possible, but not less than n.
*+	Match 0 or more times. Match as many times as possible when first encountered, do not retry with fewer even if overall match fails (Possessive Match)
++	Match 1 or more times. Possessive match
?+	Match zero or one times. Possessive match
{n}+	Match exactly n times
{n,}+	Match at least n times. Possessive Match.
{n,m}+	Match between n and m times. Possessive Match.
( ... )	Capturing parentheses. Range of input that matched the parenthesized subexpression is available after the match.
(?: ... )	Non-capturing parentheses. Groups the included pattern, but does not provide capturing of matching text. Somewhat more efficient than capturing parentheses.
(?> ... )	Atomic-match parentheses. First match of the parenthesized subexpression is the only one tried; if it does not lead to an overall pattern match, back up the search for a match to a position before the "(?>"
(?# ... )	Free-format comment (?# comment ).
(?= ... )	Look-ahead assertion. True if the parenthesized pattern matches at the current input position, but does not advance the input position.
(?! ... )	Negative look-ahead assertion. True if the parenthesized pattern does not match at the current input position. Does not advance the input position.
(?<= ... )	Look-behind assertion. True if the parenthesized pattern matches text preceding the current input position, with the last character of the match being the input character just before the current position. Does not alter the input position. The length of possible strings matched by the look-behind pattern must not be unbounded (no * or + operators.)
(?	Negative Look-behind assertion. True if the parenthesized pattern does not match text preceding the current input position, with the last character of the match being the input character just before the current position. Does not alter the input position. The length of possible strings matched by the look-behind pattern must not be unbounded (no * or + operators.)
(?ismwx-ismwx: ... )	Flag settings. Evaluate the parenthesized expression with the specified flags enabled or disabled.
(?ismwx-ismwx)	Flag settings. Change the flag settings. Changes apply to the portion of the pattern following the setting. For example, (?i) changes to a case insensitive match.

Set Expressions (Character Classes)

Example	Description
[abc]	Match any of the characters a, b or c
[^abc]	Negation - match any character except a, b or c
[A-M]	Range - match any character from A to M. The characters to include are determined by Unicode code point ordering.
[\u0000-\U0010ffff]	Range - match all characters.
[\p{Letter}]	Characters with Unicode Category = Letter. All forms shown are equivalent.
[\p{General_Category=Letter}]
[\p{L}]
[\P{Letter}]	Negated property. (Upper case \P) Match everything except Letters.
[\p{numeric_value=9}]	Match all numbers with a numeric value of 9. Any Unicode Property may be used in set expressions.
[\p{Letter}&&\p{script=cyrillic}]	Logical AND or intersection. Match the set of all Cyrillic letters.
[\p{Letter}--\p{script=latin}]	Subtraction. Match all non-Latin letters.
[[a-z][A-Z][0-9]]	Implicit Logical OR or Union of Sets. The examples match ASCII letters and digits. The two forms are equivalent.
[[a-zA-Z0-9]]
[:script=Greek:]	Alternate POSIX-like syntax for properties. Equivalent to \p{script=Greek}

Examples

• Example 1: Select the employee number where the last name is spelled LUCCHESSI, LUCHESSI, or LUCHESI from the EMPLOYEE table without considering upper or lower case letters.

  SELECT EMPNO FROM EMPLOYEE 
   WHERE REGEXP_LIKE(LASTNAME,'luc+?hes+?i','i')

  The result is 1 row with EMPNO value ‘000110’.

• Example 2: Select any invalid product identifier values from the PRODUCT table. The expected format is ‘nnn-nnn-nn’ where ‘n’ is a digit from 0 to 9.

  SELECT PID FROM PRODUCT 
   WHERE NOT REGEXP_LIKE(pid,'[0-9]{3}-[0-9]{3}-[0-9]{2}')

  The result is 0 rows because all the product identifiers match the pattern.