Script to Extract a number from a text string in Vertica

Vertica includes regular expression searches as part of their standard product. This makes it easy to extract numbers from text strings.

dbadmin=> SELECT regexp_substr('The right number is  2014.', 'd+',1) as "Right Year";
 Right Year
------------
 2014
(1 row)

The REGEXP_SUBSTR returns the sub string that matches a regular expression within a string. If no matches are found, this function returns NULL. This is different than an empty string, which can be returned by this function if the regular expression matches a zero-length string.
The regular expression to find the substring to be extracted. The syntax of the regular expression is compatible with the Perl 5 regular expression syntax. See the Perl Regular Expressions Documentation for details.