String Operations
The default character set for all strings is UTF-8.
SQL defines two primary character types: character varying(n) and
character(n), where n is a positive integer. Both of these types
can store strings up to n characters (not bytes) in length. An attempt
to store a longer string into a column of these types will result in
an error, unless the excess characters are all spaces, in which case
the string will be truncated to the maximum length. (This somewhat
bizarre exception is required by the SQL standard.) If the string to
be stored is shorter than the declared length, values of type
character will be space-padded; values of type character varying will
simply store the shorter string.
In addition, we provide the text, or varchar type, which stores
strings of any length.
String constants (literals)
A string constant in SQL is an arbitrary sequence of characters
bounded by single quotes ('), for example 'This is a string'. To
include a single-quote character within a string constant, write two
adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is
not the same as a double-quote character (").
Two string constants that are only separated by whitespace with at least one newline are concatenated and effectively treated as if the string had been written as one constant. For example:
SELECT 'foo'
'bar'
is equivalent to:
SELECT 'foobar'
but:
SELECT 'foo' 'bar'
is not valid syntax. Newlines are supported within string literals:
SELECT 'a
b'
produces a multi-line string literal.
Unicode characters
A string literal prefixed with the U& can contain Unicode characters
described using their numeric code: U&'hello\0041' includes the
Unicode character with code U+0041, which is the uppercase letter 'A'.
A Unicode character is described by an escape character followed by
four hexadecimal digits.
The default escape character is backslash, but it can be changed using
the following syntax: U&'hello!0041' UESCAPE '!'. The UESCAPE
notation designates the escape character to use in interpreting the
current literal, which is the exclamation sign in the previous
example.
Operations on string values
| Operation | Description | Examples |
|---|---|---|
|| | String concatenation (infix). Note that concatenation does not strip trailing spaces
from CHAR(N) values, unlike other SQL dialects. If such behavior is desired, an explicit
cast to varchar can be added. | 'Post' || 'greSQL' => PostgreSQL |
string LIKE pattern [ESCAPE escape-character] and
string NOT LIKE pattern [ESCAPE escape-character] | The LIKE expression returns true if string matches pattern.
(As expected, the NOT LIKE expression returns false if LIKE returns true.) | See LIKE for details. |
| The | See |
string RLIKE pattern and
string NOT RLIKE pattern | The RLIKE expression returns true if string matches pattern.
The pattern is a standard Rust regular expression (see Regular expressions).
If either argument is NULL, the result is NULL.
If the regular expression is invalid the program will crash with an error. | 'string' RLIKE 's..i.*' => TRUE |
ASCII ( string ) | Returns the numeric code of the first character of string. In UTF8 encoding, returns the Unicode code point of the character. In other multibyte encodings, the argument must be an ASCII character. Returns 0 if the string is empty. | ascii('x') => 120 |
CHAR_LENGTH(string) or CHARACTER_LENGTH(string) or LENGTH(string) or LEN(string) | Returns number of characters in string. | char_length('josé') => 4 |
CHR ( integer ) | Returns a string containing the character numbered integer. If the code is incorrect (e.g., negative), the result is an empty string. | chr(65) => A |
CONCAT(string1, ..., stringN) | String concatenation. Can have any number of arguments. | CONCAT('Post', 'greSQL', 1) => PostgreSQL1 |
CONCAT_WS(sep, string1, ..., stringN) | String concatenation with separator sep. Can have any number of arguments. sep is intercalated between all strings. If sep is NULL result is NULL. Other NULL arguments are ignored. | CONCAT_WS(',', 'Post', 'greSQL', NULL, '1') => Post,greSQL,1 |
INITCAP ( string ) | Converts the first letter of each word in string to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters. | initcap('hi THOMAS') => Hi Thomas |
INITCAP_SPACES ( string ) | Converts the first letter of each word in string to upper case and the rest to lower case. Words are sequences of characters separated by spaces. | initcap_spaces('hi THOMAS-SON') => Hi Thomas-son |
LEFT ( string, count ) | Returns first count characters in string. If any argument is NULL, returns NULL. | left('abcde', 2) => ab |
LOWER ( string ) | Converts string to all lower case. | lower('TOM') => tom |
MD5(string) | Calculates an MD5 128-bit checksum of | SELECT md5('Feldera') => 841afc2f65b5763600818ef42a56d7d1 |
OVERLAY ( string PLACING newsubstring FROM start [ FOR remove ] ) | Replaces the substring of string that starts at the start'th character and extends for remove characters with newsubstring. If remove is omitted, it defaults to the length of newsubstring. If start is not positive, the original string is unchanged. If start is bigger than the length of string, the result is the concatenation of the two strings. If remove is negative it is considered 0. | overlay('Txxxxas' placing 'hom' from 2 for 4) => Thomas |
POSITION(substring IN string) | Returns the first Unicode character index of substring within string, or zero if it's not present. First character has index 1. | position('om' in 'Thomas') => 3 |
REGEXP_REPLACE(expr, pat[, repl]) | Replaces occurrences in the string expr that match the regular expression
specified by the pattern pat with the replacement string repl, and returns
the resulting string. If any one of expr, pat, or repl is NULL, the return value is NULL.
If repl is missing, it is assumed to be the empty string. If the regular
expression is invalid, the program will crash at runtime with an error. | |
REPEAT(string, count) | Repeats string the specified number of times. The result is an empty string for a negative or 0 count. | repeat('Pg', 4) => PgPgPgPg |
REPLACE(haystack, needle, replacement) | Replaces all occurrences of needle in haystack with replacement. | replace('abcdefabcdef', 'cd', 'XX') => abXXefabXXef |
REVERSE(string) | Returns a new string with the characters of 'string' in reverse order. | reverse('Feldera') => aredleF |
RIGHT(string, count) | Returns last count characters in the string. If any argument is NULL, return NULL. | right('abcde', 2) => de |
RLIKE(string, pattern) | A function equivalent to the RLIKE operator above. | RLIKE('string', 's..i.*') => TRUE |
SPLIT(string [, delimiter]) | Produce an array of strings, by splitting string at each occurrence of delimiter.
If delimiter is empty, return an array containing just string. If
string is empty, return an empty array. If either argument is NULL, return NULL.
If delimiter is absent, a single comma is used ','. | SPLIT('a|b|c|', '|') => ['a', 'b', 'c', ''] |
SPLIT_PART(string, delimiter, n) | This function uses 1-based indexing. It extracts the
|
|
SUBSTR ( string, start [ , length ] ) | Extracts the substring of string starting at the start'th character, and stopping after length characters if the value is specified. If start is negative, the first character is chosen counting backwards from the end of string. If length is negative the empty string is returned. The index of the first character is 1. | SUBSTR('Thomas', 2, 3) => homSUBSTR('Thomas', 3) => omas |
SUBSTRING ( string FROM start [ FOR count ] ) | Extracts the substring of string starting at the start'th character, and stopping after count characters if the value is specified. If start is negative, only max(count + start - 1, 0) characters are returned. If count is negative the empty string is returned. The index of the first character is 1. | SUBSTRING('Thomas' from 2 for 3) => homSUBSTRING('Thomas' from 3) => omas |
TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string ) | Remove characters from the specified ends of string. If characters is not specified, it is replaced with a single space. | TRIM(both 'xyz' from 'yxTomxx') => TomTRIM(leading 'xyz' from 'yxTomxx') => Tomxx |
UPPER ( string ) | Converts string to all upper case. | upper('tom') => TOM |
XXHASH ( string, seed ) | Computes the hash of the specified string with the specified seed. | xxhash('abc', 2) => 6026019377950218999 |
LIKE
string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]
If pattern does not contain percent signs or underscores, then the
pattern only represents the string itself; in that case LIKE acts
like the equals operator. An underscore (_) in pattern stands for
(matches) any single character; a percent sign (%) matches any
sequence of zero or more characters.
Some examples:
'abc' LIKE 'abc' TRUE
'abc' LIKE 'a%' TRUE
'abc' LIKE '_b_' TRUE
'abc' LIKE 'c' FALSE
LIKE pattern matching always covers the entire string. Therefore, if
it's desired to match a sequence anywhere within a string, the pattern
must start and end with a percent sign.
To match a literal underscore or percent sign without matching other
characters, the respective character in pattern must be preceded by
an escape character. Plain LIKE has no escape character: without an
ESCAPE clause, every character except _ and % matches itself,
including the backslash. To use escapes, select an escape character with
the ESCAPE clause. To match the escape character itself, write two
escape characters. The escape character cannot be one of the special
pattern characters _ or %.
Some examples where the escape character is changed to #:
SELECT 'hawkeye' LIKE 'h%' ESCAPE '#' TRUE
SELECT 'hawkeye' NOT LIKE 'h%' ESCAPE '#' FALSE
SELECT 'h%' LIKE 'h#%' ESCAPE '#' TRUE
SELECT 'h%' NOT LIKE 'h#%' ESCAPE '#' FALSE
SELECT 'h%wkeye' LIKE 'h#%' ESCAPE '#' FALSE
SELECT 'h%wkeye' NOT LIKE 'h#%' ESCAPE '#' TRUE
SELECT 'h%wkeye' LIKE 'h#%%' ESCAPE '#' TRUE
SELECT 'h%wkeye' NOT LIKE 'h#%%' ESCAPE '#' FALSE
SELECT 'h%awkeye' LIKE 'h#%a%k%e' ESCAPE '#' TRUE
When either argument of LIKE, NOT LIKE is NULL, the result is NULL.
ILIKE
string ILIKE pattern
string NOT ILIKE pattern
The ILIKE expression performs a case-insensitive pattern match. If
pattern does not contain percent signs or underscores,
then the pattern represents the string itself, and ILIKE acts like
the equals operator, ignoring character case. An underscore (_) in
pattern matches any single character, while a percent
sign (%) matches any sequence of zero or more characters.
Some examples:
SELECT 'hawkeye' ILIKE 'h%' TRUE
SELECT 'hawkeye' NOT ILIKE 'h%' FALSE
SELECT 'hawkeye' ILIKE 'H%' TRUE
SELECT 'hawkeye' NOT ILIKE 'H%' FALSE
SELECT 'hawkeye' ILIKE 'H%Eye' TRUE
SELECT 'hawkeye' NOT ILIKE 'H%Eye' FALSE
SELECT 'Hawkeye' ILIKE 'h%' TRUE
SELECT 'Hawkeye' NOT ILIKE 'h%' FALSE
SELECT 'ABC' ILIKE '_b_' TRUE
SELECT 'ABC' NOT ILIKE '_b_' FALSE
When either argument of ILIKE, NOT ILIKE is NULL, the result is NULL.
Regular expressions
The implementation of regular expressions is based on the Rust
Regex library. The full
syntax supported is described in the Rust
documentation.
Rust regular expressions differ in some important respects from the regular expressions of other languages, such as Java, Python, and Postgres. Most differences come from a performance guarantee: the time to search a string is at most proportional to the size of the regular expression multiplied by the length of the string. To keep this guarantee, Rust does not support some constructs, such as look-around and backreferences. Other constructs are valid, but have a different meaning. See Differences from Java regular expressions.
The description below uses fragments from the Postgres documentation, where credit is given to Henry Spencer.
Currently, Feldera does not support SIMILAR TO regular
expressions.
A regular expression is a character sequence that is an abbreviated
definition of a set of strings (a regular set). A string is said to
match a regular expression if it is a member of the regular set
described by the regular expression. As with LIKE, pattern
characters match string characters exactly unless they are special
characters in the regular expression language, but regular
expressions use different special characters than LIKE does. Unlike
LIKE patterns, a regular expression is allowed to match anywhere
within a string, unless the regular expression is explicitly anchored
to the beginning or end of the string.
A regular expression is defined as one or more branches, separated
by |. It matches anything that matches one of the branches.
A branch is zero or more quantified atoms or constraints, concatenated. It matches a match for the first, followed by a match for the second, etc.; an empty branch matches the empty string.
A quantified atom is an atom possibly followed by a quantifier. Without a quantifier, it matches a match for the atom. With a quantifier, it can match some number of matches of the atom. An atom can be any of the possibilities shown in the table below.
| Atom | Description |
|---|---|
(re) | where re is any regular expression: matches a match for re, with the match noted for possible reporting |
(?:re) | as above, but the match is not noted for reporting (a “non-capturing” set of parentheses) |
. | matches any single character except the newline \n; with the s flag
(see Flags) it also matches \n |
[chars] | a bracket expression, matching any one of the chars (see below for more details) |
\k | where k is an ASCII character that is not a letter or a digit: matches that character
taken as an ordinary character, e.g., \\ matches a backslash character.
The exceptions are \< and \>, which are constraints (see below).
A \ followed by a non-ASCII character is illegal |
\c | where c is alphanumeric (possibly followed by other characters): is an escape, see below |
\{ | matches the left-brace character {. An unescaped { is
the beginning of a bound (see below); a { not followed by a valid bound
is rejected with a runtime error |
| x | where x is a single character with no other significance, matches that character |
The possible quantifiers and their meanings are shown in the table below.
| Quantifier | Matches |
|---|---|
* | a sequence of 0 or more matches of the atom |
+ | a sequence of 1 or more matches of the atom |
? | a sequence of 0 or 1 matches of the atom |
{m} | a sequence of exactly m matches of the atom |
{m,} | a sequence of m or more matches of the atom |
{m,n} | a sequence of m through n (inclusive) matches of the atom; m cannot exceed n |
*? | non-greedy version of * |
+? | non-greedy version of + |
?? | non-greedy version of ? |
{m}? | non-greedy version of {m} |
{m,}? | non-greedy version of {m,} |
{m,n}? | non-greedy version of {m,n} |
A constraint matches an empty string, but matches only when specific conditions are met. A constraint can be used where an atom could be used. The simple constraints are shown in the table below; some more constraints are described later.
| Constraint | Description |
|---|---|
^ | matches at the beginning of the string; with the m flag, also after each \n |
$ | matches at the end of the string; with the m flag, also before each \n |
Bracket Expressions
A bracket expression is a list of characters enclosed in []. It
normally matches any single character from the list (but see
below). If the list begins with ^, it matches any single character
not from the rest of the list. If two characters in the list are
separated by -, this is shorthand for the full range of characters
between those two (inclusive) in Unicode code-point order, e.g., [0-9]
matches any decimal digit. A - that immediately follows a range is
treated as a literal, e.g., [a-c-e] matches a, b, c, -, or e.
To include a literal ] in the list, make it the first character
(after ^, if that is used) or escape it as \]. To include a literal
-, make it the first or last character, or escape it as \-.
Unlike in POSIX bracket expressions, \ keeps its special meaning
within a bracket expression: escapes such as \], \\, and the class
shorthands such as \d work inside the brackets.
To include a literal [ in the list, escape it as \[. An unescaped
[ inside a bracket expression starts a nested bracket expression, so
[a[] is an unclosed bracket expression. A nested bracket expression
adds its characters to the list; for example, [a-d[m-p]] matches the
letters a to d and m to p. Bracket expressions also support set
operations:
| Operator | Name | Example | Matches |
|---|---|---|---|
&& | intersection | [a-z&&[^aeiou]] | characters in both lists: b, but not a |
-- | difference | [a-z--[aeiou]] | characters in the first list but not in the second: b, but not a |
~~ | symmetric difference | [a-c~~b-d] | characters in exactly one of the lists: a and d, but not b |
Within a bracket expression, the name of a character class enclosed in
[: and :] stands for the list of all characters belonging to that
class. A character class cannot be used as an endpoint of a range.
These classes contain only ASCII characters; for example, [[:alpha:]]
does not match é. The supported class names are:
| Class | Description |
|---|---|
alnum | ASCII letters and digits |
alpha | ASCII letters |
ascii | any ASCII character |
blank | space and tab |
cntrl | ASCII control characters |
digit | the digits 0 to 9 |
graph | printable ASCII characters except space |
lower | ASCII lower-case letters |
print | printable ASCII characters including space |
punct | ASCII punctuation |
space | ASCII white space |
upper | ASCII upper-case letters |
word | ASCII letters, digits, and underscore |
xdigit | hexadecimal digits |
Class-shorthand escapes provide shorthands for certain commonly-used
character classes. They are shown in the table below. Unlike the
[:name:] classes, the shorthands match Unicode characters: \d
matches ٣ (ARABIC-INDIC DIGIT THREE), but [[:digit:]] does not.
| Escape | Description |
|---|---|
\d | matches any Unicode decimal digit (\p{Nd}) |
\s | matches any Unicode white space character (\p{White_Space}) |
\w | matches any Unicode word character: letters, marks, digits, and connector punctuation such as _ |
\D | matches any character that \d does not match |
\S | matches any character that \s does not match |
\W | matches any character that \w does not match |
To make a shorthand match only ASCII characters, turn off the u
flag: (?-u:\d) matches only the digits 0 to 9.
Regular Expression Escapes
Escapes are special sequences beginning with \ followed by an
alphanumeric character. Escapes come in several varieties: character
entry, class shorthands, and constraint escapes. A
\ followed by an alphanumeric character but not constituting a valid
escape is illegal.
Character-entry escapes exist to make it easier to specify non-printing and other inconvenient characters in REs. They are shown in the table below.
| Escape | Description |
|---|---|
\a | bell (U+0007) |
\f | form feed (U+000C) |
\n | newline, as in C |
\r | carriage return, as in C |
\t | horizontal tab, as in C |
\v | vertical tab (U+000B) |
\uwxyz | (where wxyz is exactly four hexadecimal digits) the character whose hexadecimal value is 0xwxyz |
\Uwxyzabcd | (where wxyzabcd is exactly eight hexadecimal digits) the character whose hexadecimal value is 0xwxyzabcd |
\xhh | (where hh is a pair of hexadecimal digits) the character whose hexadecimal value is 0xhh; the null character is \x00 |
\x{h...} | the character whose hexadecimal value is 0xh...; for example, \x{1F600} is 😀. \u{h...} and \U{h...} are equivalent |
A constraint escape is a constraint, matching the empty string if specific conditions are met, written as an escape. They are shown in the table below.
| Escape | Description |
|---|---|
\A | matches only at the beginning of the string |
\z | matches only at the end of the string |
\b | matches word boundaries |
\B | not a word boundary |
\< | matches at the beginning of a word |
\> | matches at the end of a word |
Flags
Flags change how a regular expression matches. (?flags) sets the
flags until the end of the enclosing group, and (?flags:re) sets the
flags only for re. A - turns off the flags that follow it. For
example, (?i)a(?-i)b matches Ab, but not AB.
| Flag | Meaning |
|---|---|
i | case-insensitive matching, for all Unicode letters |
m | multi-line mode: ^ and $ also match at the beginning and the end of each line |
s | . also matches \n |
R | CRLF mode: in multi-line mode, ^ and $ also accept \r\n as a line end, and . does not match \r |
U | swaps the meaning of greedy and non-greedy quantifiers: a+ is non-greedy, and a+? is greedy |
u | Unicode mode, which is on by default; (?-u) makes \d, \s, \w, \b, and the i flag use only ASCII characters |
x | verbose mode: white space is ignored, and # starts a comment that ends at the end of the line |
Capture groups
A common way to use regexes is with capture groups. That is, instead of just looking for matches of an entire regex, parentheses are used to create groups that represent part of the match.
For example, consider a string with multiple lines, and each line has
three whitespace delimited fields where the second field is expected
to be a number and the third field a boolean. This can be expressed
with the following regular expression, where the capture groups have
been labeled $0 to $3.
(?m)^\s*(\S+)\s+([0-9]+)\s+(true|false)\s*$
^^^^^ ^^^^^^^^ ^^^^^^^^^^^^
$1 $2 $3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
$0
The (?m) flag group is non-capturing, so it receives no label.
Capture group 0 always corresponds to an implicit unnamed group that includes the entire match. If a match is found, this group is always present.
Subsequent groups may be named and are numbered, starting at 1, by the
order in which the opening parenthesis appears in the pattern. For
example, in the pattern (?<a>.(?<b>.))(?<c>.), a, b and c
correspond to capture groups $1, $2 and $3, respectively.
Differences from Java regular expressions
Feldera uses Rust regular expressions, not Java regular expressions. The table below shows Java constructs that Feldera does not support. A regular expression that uses one of them is invalid.
| Java construct | Meaning in Java | Alternative |
|---|---|---|
(?=re), (?!re) | look-ahead | none |
(?<=re), (?<!re) | look-behind | none |
\1 to \9, \k<name> | backreference | none |
(?>re) | atomic group | none |
\Q...\E | quotes the enclosed text | escape each special character with \ |
\G | the end of the previous match | none |
\Z | the end of the string, or before a final line terminator | \z matches only at the end of the string |
\R | any line break | none |
\X | an extended grapheme cluster | none |
\h, \H, \V | horizontal white space, and the opposites of \h and \v | a bracket expression |
\e | the escape character (U+001B) | \x1B |
\cA | a control character | a \x escape, such as \x01 |
\0101 | an octal escape | a \x escape, such as \x41 |
\N{...} | the character with a Unicode name | a \x{...} escape |
\p{InGreek} | a Unicode block | a range, such as [\x{370}-\x{3FF}] |
\p{javaLowerCase} | a java.lang.Character property | a Unicode property, such as \p{Lowercase} |
\p{Alnum}, \p{Blank}, \p{Graph}, \p{Print}, \p{XDigit} | an ASCII class | [[:alnum:]], [[:blank:]], [[:graph:]], [[:print:]], [[:xdigit:]] |
(?d) | Unix lines mode | none |
The table below shows Java constructs that are valid in Feldera, but have a different meaning.
| Construct | Meaning in Java | Meaning in Feldera |
|---|---|---|
\d, \s, \w, \b | ASCII characters only | Unicode characters: \d matches ٣ |
\p{Lower}, \p{Upper}, \p{Alpha}, \p{Digit}, \p{Punct}, \p{Space} | ASCII characters only | Unicode characters: \p{Lower} matches é |
(?i) | case-insensitive for ASCII letters only, unless the u flag is also set | case-insensitive for all Unicode letters: (?i)é matches É |
. | any character except a line terminator: \n, \r, U+0085, U+2028, or U+2029 | any character except \n |
$ | the end of the string, or before a final line terminator | the end of the string only, without the m flag: a$ does not match a\n |
\v | any vertical white space character | the vertical tab (U+000B) only |
[[:alpha:]] | a nested bracket expression that matches :, a, l, p, or h | an ASCII letter |
(?U) | Unicode character classes | swaps greedy and non-greedy quantifiers |
(?u) | Unicode case-insensitive matching | Unicode mode, which is on by default |
a*+, a++, a?+, a{2}+ | possessive quantifiers | a quantifier applied to a quantified atom: a*+ is (?:a*)+ |
$1x in a replacement string | capture group 1, then x | the capture group named 1x; write ${1}x instead |
\$ in a replacement string | a literal $ | a literal \, then a capture group reference; write $$ instead |
Regular expression functions
REGEXP_REPLACE(expr, pat[, repl])
If repl is missing, it is assumed to be the empty string.
Replaces occurrences in the string expr that match the regular
expression specified by the pattern pat with the replacement string
repl, and returns the resulting string. If any of expr, pat, or
repl is NULL, the return value is NULL. If the pattern cannot
be parsed as a valid regular expression the program will crash at runtime.
Replacement string syntax
All instances of $N in the replacement string are replaced with the
substring corresponding to the capture group identified by N.
N may be an integer corresponding to the index of the capture group
(counted by order of opening parenthesis where 0 is the entire match)
or it can be a name (consisting of letters, digits or underscores)
corresponding to a named capture group.
If N isn’t a valid capture group (whether the name doesn’t exist or
isn’t a valid index), then it is replaced with the empty string.
The longest possible name is used. For example, $1a looks up the
capture group named 1a and not the capture group at index 1. To
exert more precise control over the name, use braces, e.g., ${1}a.
To write a literal $ use $$.
Examples:
select regexp_replace('1078910', '[^01]');
1010
select regexp_replace('deep fried', '(?<first>\w+)\s+(?<second>\w+)', '${first}_$second');
deep_fried
select regexp_replace('Springsteen, Bruce', '([^,\s]+),\s+(\S+)', '$2 $1');
Bruce Springsteen
select regexp_replace('Springsteen, Bruce', '(?<last>[^,\s]+),\s+(?<first>\S+)', '$first $last');
Bruce Springsteen
Note that using $2 instead of $first or $1 instead of $last
would produce the same result.