Text Functions and Operators

This section describes functions and operators for examining and manipulating STRING values.

string[index]

string[begin:end]

string LIKE target

string SIMILAR TO regex

arg1 || arg2

array_extract(string, index)

array_slice(list, begin, end)

ascii(string)

bar(x, min, max[, width])

bin(string)

bit_length(string)

chr(code_point)

concat(value, ...)

concat_ws(separator, string, ...)

contains(string, search_string)

format(format, ...)

formatReadableDecimalSize(integer)

format_bytes(integer)

from_base64(string)

greatest(arg1, ...)

hash(value, ...)

hex(string)

ilike_escape(string, like_specifier, escape_character)

instr(string, search_string)

least(arg1, ...)

left(string, count)

left_grapheme(string, count)

length(string)

length_grapheme(string)

like_escape(string, like_specifier, escape_character)

lower(string)

lpad(string, count, character)

ltrim(string[, characters])

md5(string)

md5_number(string)

md5_number_lower(string)

md5_number_upper(string)

nfc_normalize(string)

not_ilike_escape(string, like_specifier, escape_character)

not_like_escape(string, like_specifier, escape_character)

parse_dirname(path[, separator])

parse_dirpath(path[, separator])

parse_filename(string[, trim_extension][, separator])

parse_path(path[, separator])

position(search_string IN string)

prefix(string, search_string)

printf(format, ...)

read_text(source)

regexp_escape(string)

regexp_extract(string, regex[, group][, options])

regexp_extract(string, regex, name_list[, options])

regexp_extract_all(string, regex[, group][, options])

regexp_full_match(string, regex[, col2])

regexp_matches(string, regex[, options])

regexp_replace(string, regex, replacement[, options])

regexp_split_to_table(string, regex)

repeat(string, count)

replace(string, source, target)

reverse(string)

right(string, count)

right_grapheme(string, count)

rpad(string, count, character)

rtrim(string[, characters])

sha1(value)

sha256(value)

split_part(string, separator, index)

starts_with(string, search_string)

string_split(string, separator)

string_split_regex(string, regex[, options])

strip_accents(string)

strlen(string)

substring(string, start[, length])

substring_grapheme(string, start[, length])

suffix(string, search_string)

to_base(number, radix[, min_length])

to_base64(blob)

translate(string, from, to)

trim(string[, characters])

unbin(value)

unhex(value)

unicode(string)

upper(string)

url_decode(string)

url_encode(string)

Text Similarity Functions

These functions are used to measure the similarity of two strings using various similarity measures.

damerau_levenshtein(s1, s2)

hamming(s1, s2)

jaccard(s1, s2)

jaro_similarity(s1, s2[, score_cutoff])

jaro_winkler_similarity(s1, s2[, score_cutoff])

levenshtein(s1, s2)

Formatters

fmt Syntax

The format(format, parameters...) function formats strings, loosely following the syntax of the {fmt} open-source formatting library.

Format without additional parameters:

SELECT format('Hello world'); -- Hello world

Format a string using {}:

SELECT format('The answer is {}', 42); -- The answer is 42

Format a string using positional arguments:

SELECT format('I''d rather be {1} than {0}.', 'right', 'happy'); -- I'd rather be happy than right.

Format Specifiers

Formatting Types

Integers:

SELECT format('{} + {} = {}', 3, 5, 3 + 5); -- 3 + 5 = 8

Booleans:

SELECT format('{} != {}', true, false); -- true != false

Format datetime values:

SELECT format('{}', DATE '1992-01-01'); -- 1992-01-01
SELECT format('{}', TIME '12:01:00'); -- 12:01:00
SELECT format('{}', TIMESTAMP '1992-01-01 12:01:00'); -- 1992-01-01 12:01:00

Format BLOB:

SELECT format('{}', BLOB '\x00hello'); -- \x00hello

Pad integers with 0s:

SELECT format('{:04d}', 33); -- 0033

Create timestamps from integers:

SELECT format('{:02d}:{:02d}:{:02d} {}', 12, 3, 16, 'AM'); -- 12:03:16 AM

Convert to hexadecimal:

SELECT format('{:x}', 123_456_789); -- 75bcd15

Convert to binary:

SELECT format('{:b}', 123_456_789); -- 111010110111100110100010101

Print Numbers with Thousand Separators

Integers:

SELECT format('{:,}',  123_456_789); -- 123,456,789
SELECT format('{:t.}', 123_456_789); -- 123.456.789
SELECT format('{:''}', 123_456_789); -- 123'456'789
SELECT format('{:_}',  123_456_789); -- 123_456_789
SELECT format('{:t }', 123_456_789); -- 123 456 789
SELECT format('{:tX}', 123_456_789); -- 123X456X789

Float, double and decimal:

SELECT format('{:,f}',    123456.789); -- 123,456.78900
SELECT format('{:,.2f}',  123456.789); -- 123,456.79
SELECT format('{:t..2f}', 123456.789); -- 123.456,79

printf Syntax

The printf(format, parameters...) function formats strings using the printf syntax.

Format without additional parameters:

SELECT printf('Hello world');

Hello world

Format a string using arguments in a given order:

SELECT printf('The answer to %s is %d', 'life', 42);

The answer to life is 42

Format a string using positional arguments %position$formatter, e.g., the second parameter as a string is encoded as %2$s:

SELECT printf('I''d rather be %2$s than %1$s.', 'right', 'happy');

I'd rather be happy than right.

Format Specifiers

Formatting Types

Integers:

SELECT printf('%d + %d = %d', 3, 5, 3 + 5); -- 3 + 5 = 8

Booleans:

SELECT printf('%s != %s', true, false); -- true != false

Format datetime values:

SELECT printf('%s', DATE '1992-01-01'); -- 1992-01-01
SELECT printf('%s', TIME '12:01:00'); -- 12:01:00
SELECT printf('%s', TIMESTAMP '1992-01-01 12:01:00'); -- 1992-01-01 12:01:00

Format BLOB:

SELECT printf('%s', BLOB '\x00hello'); -- \x00hello

Pad integers with 0s:

SELECT printf('%04d', 33); -- 0033

Create timestamps from integers:

SELECT printf('%02d:%02d:%02d %s', 12, 3, 16, 'AM'); -- 12:03:16 AM

Convert to hexadecimal:

SELECT printf('%x', 123_456_789); -- 75bcd15

Convert to binary:

SELECT printf('%b', 123_456_789); -- 111010110111100110100010101

Thousand Separators

Integers:

SELECT printf('%,d',  123_456_789); -- 123,456,789
SELECT printf('%.d',  123_456_789); -- 123.456.789
SELECT printf('%''d', 123_456_789); -- 123'456'789
SELECT printf('%_d',  123_456_789); -- 123_456_789

Float, double and decimal:

SELECT printf('%,f',   123456.789); -- 123,456.789000
SELECT printf('%,.2f', 123456.789); -- 123,456.79