PCRE_SUB scalar function¶
Returns replacement pattern REPL with substitutions from matched groups of regular expression PATTERN in TEXT starting from 1-based START.
Prototypes¶
PCRE_SUB(PATTERN VARCHAR(1000), REPL VARCHAR(4000), TEXT VARCHAR(4000), START INTEGER)
PCRE_SUB(PATTERN VARCHAR(1000), REPL VARCHAR(4000), TEXT VARCHAR(4000))
RETURNS VARCHAR(4000)
Description¶
PCRE substitution function. Given a regular expression in PATTERN, a
substitution pattern in REPL, some text to match in TEXT, and an
optional 1-based START position for the search, returns REPL with
backslash prefixed group specifications replaced by the corresponding matched
group, e.g. \0
refers to the group that matches the entire PATTERN,
\1
refers to the first capturing group in PATTERN. To include a literal
backslash in REPL double it, i.e. \\
. Returns NULL if the PATTERN
does not match TEXT.
Note that ordinary C-style backslash escapes are not interpreted by this
function within REPL, i.e. \n
will not be replaced by a newline
character. Use ordinary SQL hex-strings for this.
Parameters¶
- PATTERN
- The Perl-Compatible Regular Expression (PCRE) to search for.
- REPL
- The replacement pattern to return, after substitution of matched groups (indicated by back-slash prefixed numbers within this string).
- TEXT
- The text to search within.
- START
- The 1-based position from which to start the search. Defaults to
1
if omitted.
Examples¶
Simple searches demonstrating extraction of the matched portion of TEXT (if any):
VALUES
(PCRE_SUB('FOO', '\0', 'FOOBAR')),
(PCRE_SUB('FOO(BAR)?', '\0', 'FOOBAR')),
(PCRE_SUB('BAZ', '\0', 'FOOBAR'))
1
-------------------...
FOO
FOOBAR
-
A substitution demonstrating the extraction of an IP address from some text:
VALUES PCRE_SUB('\b(\d{1,3}(\.\d{1,3}){3})\b', '\1', 'IP address: 192.168.0.1')
1
-----------------...
192.168.0.1
A substitution demonstrating the replacement of one HTML tag with another:
VALUES PCRE_SUB('<([A-Z][A-Z0-9]*)[^>]*>(.*?)</\1>', '<I>\2</I>', '<B>BOLD!</B>')
1
------------------...
<I>BOLD!</I>
A substitution demonstrating that look-aheads do not form part of the match:
VALUES PCRE_SUB('Q(?!U)', '\0', 'QI')
1
---------------...
Q