Database Reference
In-Depth Information
7 13 SINAI SD 57061-o236 USA
18 27 SHELL ROCK IA S0670-0480 USA
24 37 NASHVILLE TN 37228-219 USA
.
.
.
SQL functions enable the use of regular expressions to extract the matching text,
such as
SUBSTRING()
, as well as update the text, such as
REGEXP_REPL()
.
/* extract ZIP code from text string */
SELECT SUBSTRING('4321A Main Street Franklin, MA 02038-2531'
FROM '[0-9]{5}-[0-9]{4}')
02038-2531
/* replace long format zip code with short format ZIP code
*/
SELECT REGEXP_REPLACE('4321A Main Street Franklin, MA
02038-2531',
'[0-9]{5}-[0-9]{4}',
SUBSTRING(SUBSTRING('4321A Main Street Franklin, MA
02038-2531'
FROM '[0-9]{5}-[0-9]{4}'),1,5) )
4321A Main Street Franklin, MA 02038
Regular expressions provide considerable flexibility in searching and modifying
text strings. However, it is quite easy to build a regular expression that does not
work entirely as intended. For example, a particular operation may work properly
with a given dataset, but future datasets may contain new cases to be handled.
Thus, it is important to thoroughly test any SQL code using regular expressions.
