Data Types - PostgreSQL: Up and Running - page 83

Example 5-4. Getting the nth element of a delimited string
SELECT split_part ( 'abc.123.z45' , '.' , 2 ) As x ;
x
--------
123
The string_to_array is useful for creating an array of elements from a delimited string.
By combining string_to_array with the unnest function, you can expand the returned
array into a set of rows, as shown in Example 5-5 .
Example 5-5. Converting delimited string to array to rows
SELECT unnest ( string_to_array ( 'abc.123.z45' , '.' )) As x ;
x
--------
abc
123
z45
Regular Expressions and Pattern Matching
PostgreSQL's regular expression support is downright fantastic. You can return matches
as tables or arrays and do fairly sophisticated replaces and updates. Back-referencing
and other fairly advanced search patterns are also supported. In this section, we'll pro‐
vide a small sampling of these. For more information, see Pattern Matching and String
Functions .
Example 5-6 shows you how to format phone numbers stored simply as contiguous
digits:
Example 5-6. Reformat a phone number using back-referencing
SELECT regexp_replace (
'6197306254' ,
'([0-9]{3})([0-9]{3})([0-9]{4})' ,
E '\(\\1\) \\2-\\3'
) As x ;
x
--------------
[phone]
The \\1 , \\2 , etc. refer to the elements in our pattern expression. We use the reverse
solidus ( \ ) to escape the parentheses. The E' construct is PostgreSQL syntax for denoting
that a string is an expression so that special characters like \ are treated literally.
Suppose some field contains text with embedded phone numbers; Example 5-7 shows
how to extract the phone numbers and turn them into rows all in one step.