ELEMENT Function
Splits the input string into elements separated by a specified separator and returns the specified number of elements concatenated by separators starting or ending at an element of a given index.
The following table lists the valid parameters:
• Index can be negative: The starting element is searched in the reverse order of the string.
element('a.b.c.d.e.f.g' , '.' , -4 , 3) = 'd.e.f'
• Number of elements can be negative: Index marks the last element in the resulting substring:
element('a.b.c.d.e.f.g' , '.' , -4 , -3) = 'b.c.d'
• Elements can be empty strings:
element('...d.e.f.g', '.' , 2, 1) = ''
element('...d.e.f.g', '.' , 2, 2) = '.'
element('...d.e.f.g', '.' , 2, 3) = '..d'
Example: Create two tables and insert values as follows:
drop table if exists srcs; \g
drop table if exists idx_num; \g
create table srcs(
i int not null,
src char(20) not null
); \g
create table idx_num(
i int not null,
idx int not null,
num int not null
); \g
insert into srcs values
(1, 'a.b.c.d.e.f.g'),
(2, '.b..d...g'); \g
insert into idx_num values
(1, 3, 1),
(2, -2, -3),
(3, 3, -2),
(4, -3, 2); \g
Use case 1: Index can negative.
select src,'.' as sep, idx, num, element(src, '.', idx, num) as element
from srcs, idx_num
where idx < 0
order by idx_num.i, srcs.i; \g
+--------------+--------+-------+----------+--------------+
|src | sep | idx | num | element |
+--------------+--------+-------+----------+--------------+
|a.b.c.d.e.f.g | . | -2 | -3 | d.e.f |
|.b..d...g | . | -2 | -3 | d.. |
|a.b.c.d.e.f.g | . | -3 | 2 | e.f |
|.b..d...g | . | -3 | 2 | . |
+--------------+--------+-------+----------+--------------+
(4 rows)
Use case 2: Number of elements can be negative.
select src, '.' as sep, idx, num, element(src, '.', idx, num) as element
from srcs, idx_num
where num < 0
order by idx_num.i, srcs.i; \g
+--------------+--------+-------+----------+--------------+
|src | sep | idx | num | element |
+--------------+--------+-------+----------+--------------+
|a.b.c.d.e.f.g | . | -2 | -3 | d.e.f |
|.b..d...g | . | -2 | -3 | d.. |
|a.b.c.d.e.f.g | . | 3 | -2 | b.c |
|.b..d...g | . | 3 | -2 | b. |
+--------------+--------+-------+----------+--------------+
(4 rows)
Use case 3: Separator can be an empty string.
select src, '' as sep, idx, num, element(src, '', idx, num) as element
from srcs, idx_num
where idx_num.i >= 3
order by idx_num.i, srcs.i; \g
+--------------+--------+-------+----------+--------------+
|src | sep | idx | num | element |
+--------------+--------+-------+----------+--------------+
|a.b.c.d.e.f.g | | 3 | -2 | .b |
|.b..d...g | | 3 | -2 | b. |
|a.b.c.d.e.f.g | | -3 | 2 | f. |
|.b..d...g | | -3 | 2 | .. |
+--------------+--------+-------+----------+--------------+
(4 rows)
Use case 4: Separator can be longer than one character.
select src, '..' as sep, int(-1) as idx, int(1) as num, element(src, '..', -1, 1 ) as element
from srcs
where i = 2; \g
+--------------+--------+-------+----------+--------------+
|src | sep | idx | num | element |
+--------------+--------+-------+----------+--------------+
|.b..d...g | .. | -1 | 1 | .g |
+--------------+--------+-------+----------+--------------+
(1 row)
Use case 5: Elements can be empty strings.
select src, '.' as sep, idx, num, element(src, '.', idx, num) as element
from srcs, idx_num
where srcs.i = 2
order by idx_num.i, srcs.i; \g
+--------------+--------+-------+----------+--------------+
|src | sep | idx | num | element |
+--------------+--------+-------+----------+--------------+
|.b..d...g | . | 3 | 1 | |
|.b..d...g | . | -2 | -3 | d.. |
|.b..d...g | . | 3 | -2 | b. |
|.b..d...g | . | -3 | 2 | . |
+--------------+--------+-------+----------+--------------+
(4 rows)
The ELEMENT function returns an error if both the source string and the separator are empty strings. It returns NULL if and only if any argument is NULL.