String Functions and Operators

type

str

str

A unicode string of text.

Any other type (except bytes) can be cast to and from a string:

db>

select <str>42;

{'42'}

db>

select <bool>'true';

{true}

db>

select "I ❤️ EdgeDB";

{'I ❤️ EdgeDB'}

Note that when a str is cast into a json, the result is a JSON string value. Same applies for casting back from json - only a JSON string value can be cast into a str:

db>

select <json>'Hello, world';

{'"Hello, world"'}

There are two kinds of string literals in EdgeQL: regular and raw. Raw string literals do not evaluate \, so \n in in a raw string is two characters \ and n.

The regular string literal syntax is 'a string' or a "a string". Two raw string syntaxes are illustrated below:

db>

select r'a raw \\\ string';

{'a raw \\\ string'}

db>

select $$something$$;

{'something'}

db> 
...

select $marker$something $$
nested \!$$$marker$;

{'something $$
nested \!$$'}

Regular strings use \ to indicate line continuation. When a line continuation symbol is encountered, the symbol itself as well as all the whitespace characters up to the next non-whitespace character are omitted from the string:

db> 
...

select 'Hello, \
        world';

{'"Hello, world"'}

operator

str[i]

str [ int64 ] -> str

String indexing.

Indexing starts at 0. Negative indexes are also valid and count from the end of the string.

db>

select 'some text'[1];

{'o'}

db>

select 'some text'[-1];

{'t'}

It is an error to attempt to extract a character at an index outside the bounds of the string:

db>

select 'some text'[10];

InvalidValueError: string index 10 is out of bounds

operator

str[from:to]

str [ int64 : int64 ] -> str

String slicing.

Indexing starts at 0. Negative indexes are also valid and count from the end of the string.

db>

select 'some text'[1:3];

{'om'}

db>

select 'some text'[-4:];

{'text'}

db>

select 'some text'[:-5];

{'some'}

db>

select 'some text'[5:-2];

{'te'}

It is perfectly acceptable to use indexes outside the bounds of a string in a slice:

db>

select 'some text'[-4:100];

{'text'}

db>

select 'some text'[-100:-5];

{'some'}

operator

str ++ str

str ++ str -> str

String concatenation.

db>

select 'some' ++ ' text';

{'some text'}

operator

str like pattern

str like str -> boolstr not like str -> bool

Case-sensitive simple string matching.

Returns true if the value V matches the pattern P and false otherwise. The operator not like is the negation of like.

The pattern matching rules are as follows:

In particular, this means that if there are no special symbols in the pattern, the operators like and not like work identical to \= and !=, respectively.

db>

select 'abc' like 'abc';

{true}

db>

select 'abc' like 'a%';

{true}

db>

select 'abc' like '_b_';

{true}

db>

select 'abc' like 'c';

{false}

db>

select 'a%%c' not like r'a\%c';

{true}

operator

str ilike pattern

str ilike str -> boolstr not ilike str -> bool

Case-insensitive simple string matching.

The operators ilike and not ilike work the same way as like and not like, except that the pattern is matched in a case-insensitive manner.

db>

select 'Abc' ilike 'a%';

{true}

function

str_lower()

std::str_lower(string: str) -> str

Return a lowercase copy of the input string.

db>

select str_lower('Some Fancy Title');

{'some fancy title'}

function

str_upper()

std::str_upper(string: str) -> str

Return an uppercase copy of the input string.

db>

select str_upper('Some Fancy Title');

{'SOME FANCY TITLE'}

function

str_title()

std::str_title(string: str) -> str

Return a titlecase copy of the input string.

Every word in the string will have the first letter capitalized and the rest converted to lowercase.

db>

select str_title('sOmE fAnCy TiTlE');

{'Some Fancy Title'}

function

str_pad_start()

std::str_pad_start(string: str, n: int64, fill: str = ‘ ‘) -> str

Return the input string padded at the start to the length n.

If the string is longer than n, then it is truncated to the first n characters. Otherwise, the string is padded on the left up to the total length n using fill characters (space by default).

db>

select str_pad_start('short', 10);

{'     short'}

db>

select str_pad_start('much too long', 10);

{'much too l'}

db>

select str_pad_start('short', 10, '.:');

{'.:.:.short'}

function

str_pad_end()

std::str_pad_end(string: str, n: int64, fill: str = ‘ ‘) -> str

Return the input string padded at the end to the length n.

If the string is longer than n, then it is truncated to the first n characters. Otherwise, the string is padded on the right up to the total length n using fill characters (space by default).

db>

select str_pad_end('short', 10);

{'short     '}

db>

select str_pad_end('much too long', 10);

{'much too l'}

db>

select str_pad_end('short', 10, '.:');

{'short.:.:.'}

function

str_trim_start()

std::str_trim_start(string: str, trim: str = ‘ ‘) -> str

Return the input string with all trim characters removed from its start.

If the trim specifies more than one character they will be removed from the beginning of the string regardless of the order in which they appear.

db>

select str_trim_start('     data');

{'data'}

db>

select str_trim_start('.....data', '.:');

{'data'}

db>

select str_trim_start(':::::data', '.:');

{'data'}

db>

select str_trim_start(':...:data', '.:');

{'data'}

db>

select str_trim_start('.:.:.data', '.:');

{'data'}

function

str_trim_end()

std::str_trim_end(string: str, trim: str = ‘ ‘) -> str

Return the input string with all trim characters removed from its end.

If the trim specifies more than one character they will be removed from the end of the string regardless of the order in which they appear.

db>

select str_trim_end('data     ');

{'data'}

db>

select str_trim_end('data.....', '.:');

{'data'}

db>

select str_trim_end('data:::::', '.:');

{'data'}

db>

select str_trim_end('data:...:', '.:');

{'data'}

db>

select str_trim_end('data.:.:.', '.:');

{'data'}

function

str_trim()

std::str_trim(string: str, trim: str = ‘ ‘) -> str

Return the input string with trim characters removed from both ends.

If the trim specifies more than one character they will be removed from both ends of the string regardless of the order in which they appear. This is the same as applying str_ltrim() and str_rtrim().

db>

select str_trim('  data     ');

{'data'}

db>

select str_trim('::data.....', '.:');

{'data'}

db>

select str_trim('..data:::::', '.:');

{'data'}

db>

select str_trim('.:data:...:', '.:');

{'data'}

db>

select str_trim(':.:.data.:.', '.:');

{'data'}

function

str_repeat()

std::str_repeat(string: str, n: int64) -> str

Repeat the input string n times.

If n is zero or negative an empty string is returned.

db>

select str_repeat('.', 3);

{'...'}

db>

select str_repeat('foo', -1);

{''}

function

str_replace()

std::str_replace(s: str, old: str, new: str) -> str

Replace all occurrences of old substring with the new one.

Given a string s find all non-overlapping occurrences of the substring old and replace them with the substring new.

db>

select str_replace('hello world', 'h', 'H');

{'Hello world'}

db>

select str_replace('hello world', 'l', '[L]');

{'he[L][L]o wor[L]d'}

db>

select str_replace('hello world', 'o', '😄');

{'hell😄 w😄rld'}

function

str_reverse()

std::str_reverse(string: str) -> str

Reverse the order of the characters in the string.

db>

select str_reverse('Hello world');

{'dlrow olleH'}

db>

select str_reverse('Hello 👋 world 😄');

{'😄 dlrow 👋 olleH'}

function

str_split()

std::str_split(s: str, delimiter: str) -> array<str>

Split string into array elements using the supplied delimiter.

db>

select str_split('1, 2, 3', ', ');

{['1', '2', '3']}

db>

select str_split('123', '');

{['1', '2', '3']}

function

re_match()

std::re_match(pattern: str, string: str) -> array<str>

Find the first regular expression match in a string.

Given an input string and a regular expression pattern find the first match for the regular expression within the string. Return the match, each match represented by an array of matched groups.

db>

select re_match(r'\w{4}ql', 'I ❤️ edgeql');

{['edgeql']}

function

re_match_all()

std::re_match_all(pattern: str, string: str) -> set of array<str>

Find all regular expression matches in a string.

Given an input string and a regular expression pattern repeatedly match the regular expression within the string. Return the set of all matches, each match represented by an array of matched groups.

db>

select re_match_all(r'a\w+', 'an abstract concept');

{['an'], ['abstract']}

function

re_replace()

std::re_replace(pattern: str, sub: str, string: str, named only flags: str=’’) -> str

Replace matching substrings in a given string.

Given an input string and a regular expression pattern replace matching substrings with the replacement string sub. Optional flag arguments can be used to specify additional regular expression flags. Return the string resulting from substring replacement.

db> 
...

select re_replace(r'l', r'L', 'Hello World',
                  flags := 'g');

{'HeLLo WorLd'}

function

re_test()

std::re_test(pattern: str, string: str) -> bool

Test if a regular expression has a match in a string.

Given an input string and a regular expression pattern test whether there is a match for the regular expression within the string. Return true if there is a match, false otherwise.

db>

select re_test(r'a', 'abc');

{true}

function

to_str()

std::to_str(val: datetime, fmt: optional str={}) -> strstd::to_str(val: duration, fmt: optional str={}) -> strstd::to_str(val: int64, fmt: optional str={}) -> strstd::to_str(val: float64, fmt: optional str={}) -> strstd::to_str(val: bigint, fmt: optional str={}) -> strstd::to_str(val: decimal, fmt: optional str={}) -> strstd::to_str(val: json, fmt: optional str={}) -> strstd::to_str(val: cal::local_datetime, fmt: optional str={}) -> strstd::to_str(val: cal::local_date, fmt: optional str={}) -> strstd::to_str(val: cal::local_time, fmt: optional str={}) -> str

Return string representation of the input value.

This is a very versatile polymorphic function that is defined for many different input types. In general, there are corresponding converter functions from str back to the specific types, which share the meaning of the format argument fmt.

When converting datetime, cal::local_datetime, cal::local_date, cal::local_time, duration this function is the inverse of to_datetime(), cal::to_local_datetime(), cal::to_local_date(), cal::to_local_time(), to_duration(), correspondingly.

For valid date and time formatting patterns see here.

db> 
...

select to_str(<datetime>'2018-05-07 15:01:22.306916-05',
              'FMDDth of FMMonth, YYYY');

{'7th of May, 2018'}

db>

select to_str(<cal::local_date>'2018-05-07', 'CCth "century"');

{'21st century'}

When converting one of the numeric types, this function is the reverse of: to_bigint(), to_decimal(), to_int16(), to_int32(), to_int64(), to_float32(), to_float64().

For valid number formatting patterns see here.

See also to_json().

db>

select to_str(123, '999999');

{'    123'}

db>

select to_str(123, '099999');

{' 000123'}

db>

select to_str(123.45, 'S999.999');

{'+123.450'}

db>

select to_str(123.45e-20, '9.99EEEE');

{' 1.23e-18'}

db>

select to_str(-123.45n, 'S999.99');

{'-123.45'}

When converting json, this function can take 'pretty' as the optional fmt argument to produce a pretty-formatted JSON string.

See also to_json().

db>

select to_str(<json>2);

{'2'}

db>

select to_str(<json>['hello', 'world']);

{'["hello", "world"]'}

db>

select to_str(<json>(a := 2, b := 'hello'), 'pretty');

{'{
    "a": 2,
    "b": "hello"
}'}

When converting arrays, a delimiter argument is required:

db>

select to_str(['one', 'two', 'three'], ', ');

{'one, two, three'}

There’s a deprecated version of std::to_str which operates on arrays, however array_join() should be used instead.

Regular Expressions

EdgeDB supports Regular expressions (REs), as defined in POSIX 1003.2. They come in two forms: BRE (basic RE) and ERE (extended RE). In addition, EdgeDB supports certain common extensions to the POSIX standard commonly known as ARE (advanced RE). More details about BRE, ERE, and ARE support can be found in PostgreSQL documentation.

For convenience, here’s a table outlining the different options accepted as the flags argument to various regular expression functions, or as embedded options in the pattern itself, e.g. '(?i)fooBAR':

Option Flags

Formatting

Some of the type converter functions take an extra argument specifying the formatting (either for converting to a str or parsing from one). The different formatting options are collected in this section.

Date and time formatting options

Some additional formatting modifiers:

Normally when parsing a string input whitespace is ignored, unless the FX prefix modifier is used. For example:

db> 
...

select cal::to_local_date(
    '2000    JUN', 'YYYY MON');

{<cal::local_date>'2000-06-01'}

db> 
...

select cal::to_local_date(
    '2000    JUN', 'FXYYYY MON');

InternalServerError: invalid value "   " for "MON"

Number formatting options

Some additional formatting modifiers: