Reference: Text Functions

CedarDB supports a variety of functions for the text data type. This page currently only describes a subset of those. See SQL features for a full list of supported functions.

Functions and Operators

string_to_table

The string_to_table function splits a string at the delimiter and replaces output words that match the null_string by null. If the delimiter is NULL, the string is split into all its characters. If the delimiter is empty, the string is not split at all. The final result is returned as a column of type text where each output word is a row.

Syntax

string_to_table(string Text, delimiter Text [, null_string Text])) -> setof Text

Examples

select string_to_table('The General Sherman tree is the largest tree in the world.', ' ', 'the');

 string_to_table
-----------------
The
General
Sherman
tree
is
NULL
largest
tree
in
NULL
world.
(11 rows)

Pattern Matching

POSIX Regular Expressions

CedarDB allows to specify POSIX regular expressions within a pattern. The following functions allow to specify such a pattern.

regexp_count

The regexp_count function counts the number of appearances of a pattern in a string. If a start parameter is provided, the search starts at that offset in the string, else from the beginning of the string. The flags parameter changes the function’s semantics; for example, the i flag makes the pattern matching case-insensitive.

Syntax

regexp_count(string Text, pattern Text [, start Integer [, flags Text]]) -> Integer

Examples

select regexp_count('The General Sherman tree is the largest tree in the world.', 'Tree', 23, 'i');

 regexp_count
--------------
1
(1 row)

regexp_instr

The regexp_instr function determines the position of a pattern in a string. If a start parameter is provided, the search starts at that offset in the string, else from the beginning of the string. If N is specified, the function determines the Nth match with the pattern, otherwise the first match is determined. If the endoption is not set or set to 0, the position of the match’s first character is returned, else it must be set to 1, implying the position after the match’s last character is returned. The flags parameter changes the function’s semantics; for example, the i flag makes the pattern matching case-insensitive. The subexpr allows to specify the subexpression of interest within the pattern and defaults to 0, which leads to identifying the position of the whole match regardless of parenthesized subexpressions.

Syntax

regexp_instr(string Text, pattern Text[, start Integer [, N Integer [, endpoint Integer [, flags Text [, subexpr Integer ]]]]]) -> Integer

Examples

select regexp_instr('The General Sherman tree is the largest tree in the world.', 'Tree', 23, 1, 1, 'i');

regexp_instr
--------------
45
(1 row)

regexp_like

The regexp_like function compares a pattern to a string. It returns true if the pattern matches the string and false if it does not. If any input is null, the output is also null. Flags change the function’s semantics; for example, the i flag makes the pattern matching case-insensitive.

Syntax

regexp_like(string Text, pattern Text [, flags Text])) -> Boolean

Examples

select regexp_like('The General Sherman tree is the largest tree in the world.', 'Tree.*Largest', 'i');

regexp_like
-------------
t
(1 row)