Database Reference
In-Depth Information
Example 5-7. Return phone numbers in piece of text as separate rows
SELECT
unnest
(
regexp_matches
(
'Cell (619)852-5083. Casa 619-730-6254. Bésame mucho.
E'
[(]
{
0
,
1
}
[
0
-
9
]
{
3
}
[)
-
.]
{
0
,
1
}
[
0
-
9
]
{
3
}
[
-
.]
{
0
,
1
}
[
0
-
9
]
{
4
}
', '
g
'
)
)
As
x
;
x
-------------
(619)852-5083
619-730-6254
The matching rules for
Example 5-7
are:
•
[(]{0,1}
: starts with 0 or 1
(
.
•
[0-9]{3}
: followed by 3 digits.
•
[)-.]{0,1}
: followed by 0 or 1 of ),-, or.
•
[0-9]{4}
: followed by 4 digits.
•
regexp_matches
returns a string array consisting of matches of a regular expres‐
sion. If you don't pass in the
g
parameter, your array will return just the first match
of the regular expression. The
g
stands for global and returns all matches of a regular
expression as separate elements.
•
unnest
explodes an array into a row set.
There are many ways to compose the same regular expression. For
instance,
\\d
is shorthand for
[0-9]
. But given the few characters
you'd save, we prefer the more descriptive longhand.
In addition to the wealth of regular-expression functions, you can use regular expres‐
sions with the
SIMILAR TO
(
~
) operators. This sequence returns all description fields
with embedded phone numbers:
SELECT
description
FROM
mytable
WHERE
description
~
E
'[(]{0,1}[0-9]{3}[)-.]{0,1}[0-9]{3}[-.]{0,1}[0-9]{4}'
;
Temporals
PostgreSQL support for temporal data is second to none. In addition to the usual dates
and times types, PostgreSQL has support for time zones, enabling the automatic han‐
dling of daylight saving time (DST) conversions by region. Specialized data types such
as
interval
offer datetime arithmetic. PostgreSQL also understands infinity and neg‐
