Database Reference
In-Depth Information
demonstrate an example in
Using MS Access with PostgreSQL
, where we show how to
make
varchar
behave without case sensitivity and still be able to use an index.
String Functions
Common string manipulations are padding (
lpad
,
rpad
), trimming whitespace (
rtrim
,
ltrim
,
trim
,
btrim
), extracting substrings (
substring
), and concatenating (
||
).
Example 5-2
demonstrates padding, and
Example 5-3
demonstrates trimming.
Example 5-2. Using lpad and rpad
SELECT
lpad
(
'ab'
,
4
,
'0'
)
As
ab_lpad
,
rpad
(
'ab'
,
4
,
'0'
)
As
ab_rpad
,
lpad
(
'abcde'
,
4
,
'0'
)
As
ab_lpad_trunc
;
ab_lpad | ab_rpad | ab_lpad_trunc
--------+---------+---------------
00ab | ab00 | abcd
lpad
truncates instead of padding if string is too long.
By default, trim functions remove spaces, but you can pass in an optional argument
indicating other characters to trim.
Example 5-3. Trimming spaces and characters
SELECT
a
As
a_before
,
trim
(
a
)
As
a_trim
,
rtrim
(
a
)
As
a_rt
,
i
As
i_before
,
ltrim
(
i
,
'0'
)
As
i_lt_0
,
rtrim
(
i
,
'0'
)
As
i_rt_0
,
trim
(
i
,
'0'
)
As
i_t_0
FROM
(
SELECT
repeat
(
' '
,
4
)
||
i
||
repeat
(
' '
,
4
)
As
a
,
'0'
||
i
As
i
FROM
gener
ate_series
(
0
,
200
,
50
)
As
i
)
As
x
;
a_before | a_trim | a_rt | i_before | i_lt_0 | i_rt_0 | i_t_0
-------------+--------+---------+----------+--------+--------+-------
0 | 0 | 0 | 00 | | |
50 | 50 | 50 | 050 | 50 | 05 | 5
100 | 100 | 100 | 0100 | 100 | 01 | 1
150 | 150 | 150 | 0150 | 150 | 015 | 15
200 | 200 | 200 | 0200 | 200 | 02 | 2
Version 9.0 introduced a helpful string aggregate function called
string_agg
, which we
demonstrate in
Example 3-11
and
Example 5-21
.
string_agg
is equivalent in concept
to the
group_concat
function in MySQL.
Splitting Strings into Arrays, Tables, or Substrings
There are a couple of useful functions in PostgreSQL for tearing strings apart.
The
split_part
function is useful for getting an element of a delimited string, as shown
in
Example 5-4
.
