PCRE_SEARCH scalar function

Searches for regular expression PATTERN within TEXT starting at 1-based START.

Prototypes

PCRE_SEARCH(PATTERN VARCHAR(1000), TEXT VARCHAR(4000), START INTEGER)
PCRE_SEARCH(PATTERN VARCHAR(1000), TEXT VARCHAR(4000))

RETURNS INTEGER

Description

PCRE searching function. Given a regular expression in PATTERN, and some text to search in TEXT, returns the 1-based position of the first match. START is an optional 1-based position from which to start the search (defaults to 1 if not specified). If no match is found, the function returns zero. If PATTERN, TEXT, or START is NULL, the result is NULL.

Parameters

PATTERN
The Perl-compatible Regular Expression (PCRE) to search for
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 showing the return value is a 1-based position or 0 in the case of failure:

VALUES
  (PCRE_SEARCH('FOO', 'FOOBAR')),
  (PCRE_SEARCH('BAR', 'FOOBAR')),
  (PCRE_SEARCH('BAZ', 'FOOBAR'))
1
----------
         1
         4
         0

A search to check whether a value looks vaguely like an IP address; note that the octets are not checked for 0-255 range:

VALUES PCRE_SEARCH('^\d{1,3}(\.\d{1,3}){3}$', '192.168.0.1')
1
----------
         1

A search demonstrating use of back-references to check that a closing tag matches the opening tag:

VALUES PCRE_SEARCH('<([A-Z][A-Z0-9]*)[^>]*>.*?</\1>', '<B>BOLD!</B>')
1
----------
         1

Searches demonstrating negative look-aheads:

VALUES
  (PCRE_SEARCH('Q(?!U)', 'QUACK')),
  (PCRE_SEARCH('Q(?!U)', 'QI'))
1
----------
         0
         1