Text functions

Change case, cut, trim, pad, search and join text. A letter of any language (Nepali, Chinese, ...) counts as one character.

• Category: Functions — text
• Keywords: text, string, STRING, ascii, chr, translate, overlay, PLACING, normalize, NFC, NFD, NFKC, NFKD, U&, to_ascii, remove accents, bit_length, to_hex, parse_ident, string_to_table, number format, format a number, VARCHAR, UTF-8, Unicode, Nepali, Devanagari, characters, letters, bytes, upper case, lower case, cut text, clean text, ||, join text, concatenate, lcase, ucase, len, mid, instr, locate, position IN, lengthb, format, sprintf, printf, string format, quote a name, quote a value, quote_ident, quote_literal, quote_nullable, SQL injection
• See also: Regular expressions, WHERE, Conversion functions, Bytes, hex and base64, Full-text search

These functions work on text (STRING). KDB keeps text as UTF-8. UTF-8 is the usual way to store the letters of every language. An English letter takes 1 byte; a Nepali letter like न takes 3 bytes.

• Text functions count characters, not bytes. length('नेपाल') is 5. Only octet_length counts bytes.
• A NULL argument gives NULL. Only concat, concat_ws, format and quote_nullable take NULLs.
• A number, date or true/false is first turned into text: length(12.50) is 4, because the text is 12.5.
• upper, lower and initcap change the letters of every script that has capitals: English, Ä, ě, Vietnamese, Greek, Cyrillic, Armenian and more. Nepali (Devanagari) has no capitals, so it stays as it is.
• Searching (replace, strpos, starts_with, ...) cares about upper and lower case. To ignore case, use lower() on both sides, ILIKE or a regular expression with the flag 'i'.
• To find text with a pattern, use LIKE and ILIKE or the regular expression functions.

Example: Clean labels for the company list (sample data: sample)

SELECT symbol,
       lower(symbol) AS url_part,
       split_part(name, ' ', 1) AS first_word,
       length(name) AS letters,
       symbol || ' - ' || left(name, 20) AS label
FROM securities
ORDER BY symbol;

Result:

(5 rows)

lower

Syntax:

lower(text)

The text with every capital letter made small: lower('CITY') is city, lower('ÄÖ') is äö, lower('ΣΑΣ') is σασ. Letters without capitals (Nepali) stay as they are.

• Returns: STRING.
• NULL: lower(NULL) is NULL.
• Notes: each letter becomes one letter, as in PostgreSQL: lower('İ') is i.

Example

SELECT lower('CITY Bank') AS a, lower('नेपाल') AS nepali, lower('ÄBC') AS german, lower('ПРИВЕТ') AS russian, lower(NULL) AS b;

Result:

(1 row)

Example: Log in with the user name in any case

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, username STRING NOT NULL, email STRING);
INSERT INTO app_users (username, email) VALUES ('Ram_Sharma', 'ram@example.com'), ('sita', 'sita@example.com');

SELECT id, username FROM app_users WHERE lower(username) = lower('RAM_sharma');

Result:

(1 row)

upper

Syntax:

upper(text)

The text with every small letter made capital: upper('city') is CITY, upper('ěšč') is ĚŠČ. Letters without capitals (Nepali) stay as they are.

• Returns: STRING.
• NULL: upper(NULL) is NULL.
• Notes: upper('ß') stays ß, as in PostgreSQL (one letter becomes one letter).

Example

SELECT upper('city नेपाल') AS a, upper('Coast') AS b, upper('crème brûlée') AS french, upper(NULL) AS c;

Result:

(1 row)

Example: Clean stock symbols typed by users

Setup:

CREATE TABLE watchlist (id INT PRIMARY KEY AUTO, typed STRING);
INSERT INTO watchlist (typed) VALUES ('  city '), ('Coast'), ('volt  ');

SELECT id, '[' || typed || ']' AS typed, upper(trim(typed)) AS symbol FROM watchlist ORDER BY id;

Result:

(3 rows)

length, char_length, character_length

Syntax:

length(text)
char_length(text)
character_length(text)

The number of characters in the text. A Nepali letter is one character. The three names are the same function.

• Returns: LONG.
• NULL: length(NULL) is NULL.
• Notes: on a BYTES value it counts bytes (see bytes functions). Some Nepali letters are made of 2 characters: के is क plus the sign े, so length('के') is 2.

Example

SELECT length('CITY') AS a, length('नेपाल') AS nepali, length('के') AS ke, char_length('') AS empty, character_length('City Bank') AS b, length(12.50) AS number, length(NULL) AS c;

Result:

(1 row)

Example: Refuse a forum post that is too short or too long

SELECT body, length(body) AS letters, length(body) BETWEEN 10 AND 500 AS ok
FROM (SELECT 'Good results!' AS body
      UNION ALL SELECT 'नाफा राम्रो छ'
      UNION ALL SELECT 'ok') AS posts;

Result:

(3 rows)

Example: Company names in English and Nepali

Setup:

CREATE TABLE companies (symbol STRING PRIMARY KEY, name STRING, name_ne STRING);
INSERT INTO companies VALUES ('CITY', 'City Bank Limited', 'सिटी बैंक लिमिटेड'), ('VOLT', 'Volt River Hydropower Limited', 'भोल्ट रिभर जलविद्युत लिमिटेड');

SELECT symbol, length(name) AS name_letters, name_ne, length(name_ne) AS ne_letters FROM companies ORDER BY symbol;

Result:

(2 rows)

octet_length

Syntax:

octet_length(text)

The number of bytes the text takes in UTF-8. An English letter is 1 byte; a Nepali letter is 3 bytes.

• Returns: LONG.
• NULL: octet_length(NULL) is NULL.
• Notes: use it when a limit is in bytes (for example a column in another program that takes at most 255 bytes). For letters people see, use length.

Example

SELECT length('नेपाल') AS letters, octet_length('नेपाल') AS bytes, octet_length('CITY') AS english_bytes, octet_length('') AS empty;

Result:

(1 row)

substr, substring

Syntax:

substr(text, start)
substr(text, start, count)
substring(text, start [, count])

A part of the text: count characters from character number start. The first character is number 1. Without count, you get everything to the end. substring is the same function.

• Returns: STRING (BYTES for a BYTES value, counted in bytes).
• NULL: NULL when any argument is NULL.
• Notes: start after the end gives empty text '', not an error. A negative count is an error. start and count must be whole numbers: '2' or 2.5 is an error. The PostgreSQL form substring(text FROM 2 FOR 3) works too, and means the same as substring(text, 2, 3).

Example

SELECT substr('CITY', 3) AS a, substring('CITY', 2, 3) AS b, substr('नेपाल', 2, 2) AS nepali, substr('CITY', 0, 3) AS from_zero, substr('CITY', 10) AS after_end;

Result:

(1 row)

Example: Nepali fiscal year 2082/83: first and last year

SELECT substr('2082/83', 1, 4) AS first_year, substr('2082/83', 6) AS second_year;

Result:

(1 row)

Example: A negative count is an error (shows an error)

SELECT substr('CITY', 2, -1);

Result:

Error: line 1, column 8: substr: the length must not be negative, got -1

Example: The PostgreSQL FROM ... FOR form

SELECT substring('CITY' FROM 2 FOR 3) AS from_for, substring('CITY' FROM 2) AS to_the_end;

Result:

(1 row)

left

Syntax:

left(text, n)

The first n characters of the text. With a negative n, everything except the last -n characters.

• Returns: STRING.
• NULL: NULL when any argument is NULL.
• Notes: it never cuts a Nepali letter in half. n bigger than the text gives the whole text.

Example

SELECT left('CITY', 3) AS a, left('CITY', -2) AS drop_last_2, left('नेपाल बैंक', 5) AS nepali, left('CITY', 99) AS whole;

Result:

(1 row)

Example: Short preview of long company names (sample data: sample)

SELECT symbol,
       CASE WHEN length(name) > 25 THEN left(name, 25) || '...' ELSE name END AS preview
FROM securities
ORDER BY symbol;

Result:

(5 rows)

right

Syntax:

right(text, n)

The last n characters of the text. With a negative n, everything except the first -n characters.

• Returns: STRING.
• NULL: NULL when any argument is NULL.

Example

SELECT right('CITY', 3) AS a, right('CITY', -2) AS drop_first_2, right('नेपाल', 2) AS nepali;

Result:

(1 row)

Example: Show only the last 4 digits of a phone number

SELECT '******' || right('9841234567', 4) AS phone_shown;

Result:

(1 row)

trim, btrim

Syntax:

trim(text)
trim(text, characters)
btrim(text [, characters])

Removes spaces from both ends of the text. With characters, it removes any of those characters from both ends instead. btrim is the same function.

• Returns: STRING.
• NULL: NULL when any argument is NULL.
• Notes: characters is a set of single characters, not a word: trim('xyCITYyx', 'xy') removes every x and y at the ends. Characters in the middle stay. The standard forms trim(BOTH 'x' FROM text), trim(LEADING 'x' FROM text) and trim(TRAILING 'x' FROM text) work too; LEADING is ltrim and TRAILING is rtrim.

Example

SELECT '[' || trim('  CITY  ') || ']' AS a, trim('xxCOASTxx', 'x') AS b, btrim('--a-b--', '-') AS c, trim('xyCITYyx', 'xy') AS d;

Result:

(1 row)

Example: Clean a user name before saving it

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, username STRING NOT NULL);

INSERT INTO app_users (username) VALUES (lower(trim('   Hari_KC  ')));
SELECT id, '[' || username || ']' AS saved FROM app_users;

Result:

INSERT INTO app_users (username) VALUES (lower(trim(' Hari_KC ')))

1 row(s) inserted

SELECT id, '[' || username || ']' AS saved FROM app_users

(1 row)

Example: The standard BOTH / LEADING / TRAILING forms

SELECT trim(BOTH 'x' FROM 'xxCOASTxx') AS both_ends,
       trim(LEADING 'x' FROM 'xxCOASTxx') AS front_only,
       trim(TRAILING 'x' FROM 'xxCOASTxx') AS end_only;

Result:

(1 row)

ltrim

Syntax:

ltrim(text [, characters])

Like trim, but only at the start (left side) of the text.

• Returns: STRING.
• NULL: NULL when any argument is NULL.

Example

SELECT '[' || ltrim('   CITY  ') || ']' AS a, ltrim('00042', '0') AS no_zeros;

Result:

(1 row)

rtrim

Syntax:

rtrim(text [, characters])

Like trim, but only at the end (right side) of the text.

• Returns: STRING.
• NULL: NULL when any argument is NULL.

Example

SELECT '[' || rtrim('  CITY   ') || ']' AS a, rtrim('नेपाल।।', '।') AS nepali_full_stop, rtrim('512.500', '0') AS price;

Result:

(1 row)

replace

Syntax:

replace(text, from, to)

Replaces every place where from is found in text with to.

• Returns: STRING.
• NULL: NULL when any argument is NULL.
• Notes: an empty from changes nothing. The search does not ignore case: replace('City', 'city', 'x') is City. For patterns (any digit, any space) use regexp_replace.

Example

SELECT replace('a-b-c', '-', '/') AS a, replace('City Bank', 'Bank', 'Bank Limited') AS b, replace('City', 'city', 'x') AS case_matters, replace('CITY', '', 'x') AS empty_from;

Result:

(1 row)

Example: Turn a price with commas into a number

SELECT CAST(replace('1,234,567.50', ',', '') AS DOUBLE) AS amount;

Result:

(1 row)

split_part

Syntax:

split_part(text, separator, n)

Splits the text at every separator and gives part number n. 1 is the first part, -1 is the last part.

• Returns: STRING. A part that does not exist gives empty text ''.
• NULL: NULL when any argument is NULL.
• Notes: an empty separator gives the whole text as part 1. PostgreSQL also allows negative n (since version 14); MySQL has no split_part.

Example

SELECT split_part('2082/83', '/', 1) AS a, split_part('2082/83', '/', -1) AS last, split_part('a,b', ',', 5) AS missing;

Result:

(1 row)

Example: The domain of each user's email

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, email STRING NOT NULL);
INSERT INTO app_users (email) VALUES ('ram@example.com'), ('sita@mail.com.np'), ('hari@example.com');

SELECT split_part(email, '@', 2) AS domain, count(*) AS users FROM app_users GROUP BY split_part(email, '@', 2) ORDER BY domain;

Result:

(2 rows)

Example: Part number 0 does not exist (shows an error)

SELECT split_part('2082/83', '/', 0);

Result:

Error: line 1, column 8: split_part: the field number must not be 0 (1 = first, -1 = last)

strpos

Syntax:

strpos(text, part)

Where part first starts in text, as a character number (1 = first). 0 when it is not there.

• Returns: LONG.
• NULL: NULL when any argument is NULL.
• Notes: the number counts characters, so it works with Nepali text. An empty part gives 1. position(part IN text) does the same with the arguments the other way round.

Example

SELECT strpos('CITY', 'IT') AS a, strpos('नेपाल बैंक', 'बैंक') AS nepali, strpos('CITY', 'x') AS not_found, strpos('City', 'cit') AS case_matters;

Result:

(1 row)

Example: The user name before the @ of an email

SELECT email, left(email, strpos(email, '@') - 1) AS name
FROM (SELECT 'ram.sharma@example.com' AS email UNION ALL SELECT 'sita@mail.com.np') AS e;

Result:

(2 rows)

position

Syntax:

position(part IN text)
position(part, text)

Where part first starts in text, as a character number (1 = first). 0 when it is not there. The same as strpos(text, part).

• Returns: LONG.
• NULL: NULL when any argument is NULL.
• Notes: part comes first here, but second in strpos.

Example

SELECT position('बैंक' IN 'नेपाल बैंक') AS nepali, position('TY' IN 'CITY') AS a, position('TY', 'CITY') AS b, position('x' IN 'CITY') AS not_found;

Result:

(1 row)

starts_with

Syntax:

starts_with(text, prefix)

true when the text begins with prefix.

• Returns: BOOL.
• NULL: NULL when any argument is NULL.
• Notes: an empty prefix is always true. text LIKE 'NA%' gives the same answer, and it can use an index.

Example

SELECT starts_with('CITY', 'CI') AS a, starts_with('city', 'CI') AS case_matters, starts_with('CITY', '') AS empty;

Result:

(1 row)

Example: Companies whose symbol starts with N (sample data: sample)

SELECT symbol, name FROM securities WHERE starts_with(symbol, 'N') ORDER BY symbol;

Result:

(0 rows)

ends_with

Syntax:

ends_with(text, suffix)

true when the text ends with suffix.

• Returns: BOOL.
• NULL: NULL when any argument is NULL.

Example

SELECT ends_with('CITY', 'TY') AS a, ends_with('CITY', 'ty') AS case_matters;

Result:

(1 row)

Example: Users with an email address in Nepal (.np)

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, email STRING NOT NULL);
INSERT INTO app_users (email) VALUES ('ram@example.com'), ('sita@mail.com.np'), ('hari@northwind.example.com');

SELECT id, email FROM app_users WHERE ends_with(lower(email), '.np') ORDER BY id;

Result:

(1 row)

lpad

Syntax:

lpad(text, length)
lpad(text, length, fill)

Makes the text length characters long by adding fill on the left. When the text is longer, it is cut to length.

• Returns: STRING.
• NULL: NULL when any argument is NULL.
• Notes: length of 0 or less gives ''. An empty fill gives the text unchanged. A text that is too long is cut at the end (like PostgreSQL).

Example

SELECT lpad('7', 3, '0') AS a, '[' || lpad('5', 3) || ']' AS spaces, lpad('ab', 7, 'xy') AS b, lpad('CITY', 3) AS cut, lpad('न', 3, 'क') AS nepali;

Result:

(1 row)

Example: Invoice numbers with leading zeros

Setup:

CREATE TABLE invoices (id INT PRIMARY KEY AUTO, amount DECIMAL(2));
INSERT INTO invoices (amount) VALUES (1500.00), (250.50), (99999.99);

SELECT 'INV-2082-' || lpad(id, 5, '0') AS invoice_no, amount FROM invoices ORDER BY id;

Result:

(3 rows)

Example: The length has a limit (shows an error)

SELECT lpad('a', 20000000);

Result:

Error: line 1, column 8: lpad: the length is larger than 10000000

rpad

Syntax:

rpad(text, length)
rpad(text, length, fill)

Makes the text length characters long by adding fill on the right. When the text is longer, it is cut to length.

• Returns: STRING.
• NULL: NULL when any argument is NULL.
• Notes: the same rules as lpad.

Example

SELECT rpad('CITY', 8, '.') AS a, rpad('CITY', 3) AS cut, '[' || rpad('COAST', 6) || ']' AS spaces;

Result:

(1 row)

Example: A simple fixed-width price list (sample data: sample)

SELECT rpad(symbol, 8, '.') || lpad(close, 8) AS line
FROM ochl_day
WHERE time = '2026-09-09'
ORDER BY symbol;

Result:

(3 rows)

repeat

Syntax:

repeat(text, n)

The text written n times one after another.

• Returns: STRING.
• NULL: NULL when any argument is NULL.
• Notes: the result can be at most 16 MB; a bigger one is an error.

Example

SELECT repeat('ab', 3) AS a, repeat('नम', 2) AS nepali, repeat('-', 0) AS zero;

Result:

(1 row)

Example: A text bar chart of the CITY close price (sample data: sample)

SELECT time, close, repeat('#', floor((close - 500) / 2)) AS bar
FROM ochl_day
WHERE symbol = 'CITY'
ORDER BY time
LIMIT 6;

Result:

(6 rows)

reverse

Syntax:

reverse(text)

The characters of the text in the opposite order: reverse('CITY') is YTIC.

• Returns: STRING (BYTES for a BYTES value, byte by byte).
• NULL: reverse(NULL) is NULL.
• Notes: it works character by character. A Nepali vowel sign is its own character, so it lands on the other side of its letter: the result of Nepali text does not read well.

Example

SELECT reverse('CITY') AS a, reverse('12345') AS b, reverse('नेपाल') AS nepali;

Result:

(1 row)

concat

Syntax:

concat(value, value, ...)

Joins the values into one text. NULL values are skipped. Numbers, dates and times are turned into text first.

• Returns: STRING. Never NULL: when every value is NULL, the result is ''.
• NULL: NULL values are skipped.
• Notes: a || b also joins text, but it gives NULL when one side is NULL. concat() with no values is an error.

Example

SELECT concat('CITY', '-', 2082) AS a, concat('a', NULL, 'b') AS null_skipped, concat(NULL) AS all_null, concat('Close: ', 512.5) AS b;

Result:

(1 row)

Example: Full name when the middle name is missing

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, first_name STRING, middle_name STRING, last_name STRING);
INSERT INTO app_users (first_name, middle_name, last_name) VALUES ('Ram', 'Bahadur', 'Thapa'), ('Sita', NULL, 'Sharma');

SELECT id,
       concat(first_name, ' ', middle_name, ' ', last_name) AS with_concat,
       first_name || ' ' || middle_name || ' ' || last_name AS with_pipes
FROM app_users ORDER BY id;

Result:

(2 rows)

concat_ws

Syntax:

concat_ws(separator, value, value, ...)

Joins the values with separator between them. NULL values are skipped, and they get no separator. "ws" means "with separator".

• Returns: STRING.
• NULL: a NULL separator gives NULL. NULL values are skipped.

Example

SELECT concat_ws(', ', 'a', NULL, 'b') AS a, concat_ws('/', 2082, 83) AS fiscal_year, concat_ws(NULL, 'a', 'b') AS null_separator;

Result:

(1 row)

Example: The full name from the example above, without double spaces

Setup:

CREATE TABLE app_users (id INT PRIMARY KEY AUTO, first_name STRING, middle_name STRING, last_name STRING);
INSERT INTO app_users (first_name, middle_name, last_name) VALUES ('Ram', 'Bahadur', 'Thapa'), ('Sita', NULL, 'Sharma');

SELECT id, concat_ws(' ', first_name, middle_name, last_name) AS full_name FROM app_users ORDER BY id;

Result:

(2 rows)

format

Syntax:

format(text, value, value, ...)

Fills the places in text with the values, as PostgreSQL does. Its main use is SQL text for EXECUTE in a procedure.

• Returns: STRING. A NULL text gives NULL.
• Notes: text made with %I and %L is safe to run as SQL: a quote inside a value stays part of the value. Too few values, or another letter after %, is an error.

Example

SELECT format('%s has %s rows', 'trades', 120) AS plain,
       format('SELECT * FROM %I WHERE symbol = %L', 'Daily Prices', 'it''s') AS safe_sql,
       format('[%-6s][%6s][%L]', 'ab', 'cd', NULL) AS widths;

Result:

(1 row)

Example: A NULL cannot be a name (shows an error)

SELECT format('SELECT * FROM %I', NULL);

Result:

Error: line 1, column 8: format: %I of format() got NULL: a NULL cannot be a table or column name

quote_ident

Syntax:

quote_ident(name)

The name as it can be written in SQL: as it is when it is all small letters, digits and _ and not a reserved word, otherwise in double quotes (with " doubled). quote_ident('close') is close; quote_ident('Close') is "Close".

• Returns: STRING.

quote_literal, quote_nullable

Syntax:

quote_literal(value)
quote_nullable(value)

The value as SQL text in single quotes, with every ' doubled: quote_literal('it''s') is 'it''s'. A number is quoted too.

• Returns: STRING.
• NULL: quote_literal(NULL) is NULL; quote_nullable(NULL) is the text NULL, so the SQL still works.

Example

SELECT quote_ident('close') AS a, quote_ident('Daily Prices') AS b, quote_literal('O''Brien') AS c,
       quote_literal(42) AS d, quote_nullable(NULL) AS e;

Result:

(1 row)

Example: One CSV line per trading day (sample data: sample)

SELECT concat_ws(',', symbol, open, high, low, close) AS csv_line
FROM ochl_day
WHERE symbol = 'COAST'
ORDER BY time
LIMIT 3;

Result:

(3 rows)

|| (join text)

Syntax:

text || text

Joins two values into one text: 'CITY' || '-' || 2082 is CITY-2082. A number or date on one side is turned into text.

• Returns: STRING. Two BYTES values give BYTES.
• NULL: NULL on either side gives NULL. Use concat or coalesce when a value can be NULL.
• Notes: || has the same priority as + and -, and they are done from left to right. So 'a' || 1 + 2 is ('a' || 1) + 2, which is an error. PostgreSQL does + first. Write 'a' || (1 + 2). MySQL reads || as OR unless a setting is changed; KDB always joins text.

Example

SELECT 'CITY' || '-' || 2082 AS a, 'Close: ' || 512.5 AS b, 'a' || NULL AS with_null, 'total ' || (1 + 2) AS brackets;

Result:

(1 row)

Example: A link to each news article

Setup:

CREATE TABLE news (id LONG PRIMARY KEY AUTO, title STRING NOT NULL, symbol STRING);
INSERT INTO news (title, symbol) VALUES ('City Bank profit rises 18%', 'CITY'), ('Market index closes at 2,840', NULL);

SELECT id, '/news/' || id || '-' || lower(replace(left(title, 20), ' ', '-')) AS url,
       'Stock: ' || symbol AS stock_line,
       'Stock: ' || coalesce(symbol, 'market') AS fixed
FROM news ORDER BY id;

Result:

(2 rows)

Example: Without brackets, + is done after || (shows an error)

SELECT 'total ' || 1 + 2;

Result:

Error: line 1, column 22: 'total 1' and '2' are not both numbers

initcap

Syntax:

initcap(text)

Every word gets a capital first letter and small letters after it: initcap('city bank limited') is City Bank Limited.

• Returns: STRING.
• NULL: initcap(NULL) is NULL.
• Notes: a word ends at anything that is not a letter or a digit, so ram-bahadur becomes Ram-Bahadur. Letters of every script with capitals change (élan becomes Élan); Nepali letters stay as they are. Short words like "and" are capitalised too, so a company name that is already written correctly (Coastal Bank) is better left alone.

Example: Tidy up a name a user typed

SELECT initcap('city bank limited') AS company,
       initcap('RAM BAHADUR SHRESTHA') AS person,
       initcap('ram-bahadur kc') AS with_hyphen,
       initcap(NULL) AS nothing;

Result:

(1 row)

Example: Company names in title case (sample data: sample)

SELECT symbol, name, initcap(lower(name)) AS title_case
FROM securities
ORDER BY symbol;

Result:

(5 rows)

md5

Syntax:

md5(text)

The MD5 hash of the text: 32 lower-case hex digits. The same text always gives the same digits.

• Returns: STRING, always 32 characters.
• NULL: md5(NULL) is NULL. md5('') is d41d8cd98f00b204e9800998ecf8427e.
• Notes: never use MD5 for passwords. It is broken for that: use password_hash. MD5 is fine as a short fingerprint of a row, to see whether a file or a record changed. It is here because PostgreSQL apps call it. For a strong fingerprint use sha256.

Example: A short fingerprint for each daily candle (sample data: sample)

SELECT symbol, close, md5(symbol || ':' || CAST(time AS STRING)) AS row_key
FROM ochl_day
WHERE time IN '2026-09-10'
ORDER BY symbol;

Result:

(3 rows)

Example: The same text always gives the same digits

SELECT md5('CITY') AS a, md5('CITY') = md5('CITY') AS always_same, md5('') AS empty_text, md5(NULL) AS nothing;

Result:

(1 row)

to_str

Syntax:

to_str(time, format)

Writes a time as text in the QuestDB format letters. QuestDB apps use this name; KDB's own name for the same job is to_char.

• Returns: STRING.
• NULL: NULL when the time or the format is NULL.
• Notes: everything is UTC. For a local clock add its offset first, for example 20700000 for Nepal: to_str(time + 20700000, 'HH:mm'). Letters KDB does not know are written as they are. UUU (microseconds) is always 000, because KDB keeps milliseconds.

Example: Minute candles with a readable clock (sample data: sample)

SELECT to_str(time, 'yyyy-MM-dd') AS day,
       to_str(time, 'HH:mm') AS utc_clock,
       to_str(time + 20700000, 'HH:mm') AS nepal_clock,
       close
FROM ochl_minutes
WHERE symbol = 'CITY'
ORDER BY time
LIMIT 3;

Result:

(3 rows)

Example: A long date for a report heading (sample data: sample)

SELECT to_str(time, 'EEE dd MMM yyyy') AS trading_day, symbol, close
FROM ochl_day
WHERE time IN '2026-09-10'
ORDER BY symbol;

Result:

(3 rows)

string_to_array

Syntax:

string_to_array(text, separator)
string_to_array(text, separator, null_text)

Cuts the text into a list at every separator. The PostgreSQL way to turn 'CITY,COAST' into a list of symbols.

• Returns: an array (a JSON list). See arrays.
• NULL: string_to_array(NULL, ',') is NULL. Empty text gives an empty list [].
• Notes: an empty separator gives a list with the whole text in it. To go the other way use array_to_string; to make rows out of the list use unnest. To take one part only, split_part is simpler.

Example: A watchlist typed as one line

SELECT string_to_array('CITY,COAST,VOLT', ',') AS symbols,
       cardinality(string_to_array('CITY,COAST,VOLT', ',')) AS how_many,
       string_to_array('CITY,,VOLT', ',', '') AS empty_part_is_null,
       string_to_array('MARKET', NULL) AS every_character;

Result:

(1 row)

Example: Show the companies of a typed watchlist (sample data: sample)

SELECT symbol, name, sector
FROM securities
WHERE symbol IN (SELECT unnest(string_to_array('CITY,VOLT', ',')))
ORDER BY symbol;

Result:

(2 rows)

string_to_table

Syntax:

string_to_table(text, separator)
string_to_table(text, separator, null_text)

The same cut as string_to_array, but one row per part instead of one list. Use it in FROM or in the SELECT list.

• Returns: rows of STRING, one per part. In FROM the column is named after the function, or after the alias: AS t(sym).
• NULL: NULL text gives no rows. Empty text gives no rows.
• Notes: in FROM the arguments must be constants. For a column write SELECT id, string_to_table(col, ',') FROM t.

Example: A typed watchlist as rows

SELECT sym FROM string_to_table('CITY,COAST,VOLT', ',') AS t(sym);

Result:

(3 rows)

ascii

Syntax:

ascii(text)

The number of the first character of the text (its Unicode code point). ascii('A') is 65, ascii('न') is 2344.

• Returns: LONG. Empty text gives 0.
• NULL: ascii(NULL) is NULL.

Example

SELECT ascii('A') AS a, ascii('é') AS e_accent, ascii('नेपाल') AS na, ascii('') AS empty;

Result:

(1 row)

chr

Syntax:

chr(number)

The character with this Unicode number: chr(65) is A, chr(2344) is न. The opposite of ascii.

• Returns: STRING of one character.
• Errors (as PostgreSQL, SQLSTATE 54000): 0 ("null character not permitted"), a negative number, a number above 1114111, and the numbers 55296-57343 (they are not characters on their own).

Example

SELECT chr(65) AS a, chr(2344) AS na, chr(10) = E'\n' AS newline;

Result:

(1 row)

Example: 0 is not a character (shows an error)

SELECT chr(0);

Result:

Error: line 1, column 8: chr: null character not permitted

translate

Syntax:

translate(text, from, to)

Swaps single characters: each character of from becomes the character at the same place in to. A character of from with no partner in to is removed.

• Returns: STRING.
• Notes: translate('12345', '143', 'ax') is a2x5: 1 becomes a, 4 becomes x, 3 is removed. A character named twice in from uses its first partner. It works on characters of any language. When from and to are written in the query, the swap table is made once for the whole query.

Example

SELECT translate('12345', '143', 'ax') AS pg_example,
       translate('résumé', 'é', 'e') AS no_accents,
       translate('98-41-234567', '-', '') AS digits_only;

Result:

(1 row)

overlay

Syntax:

overlay(text PLACING new_text FROM start)
overlay(text PLACING new_text FROM start FOR count)
overlay(text, new_text, start [, count])

Puts new_text into text at character start, in place of count characters. Without FOR, as many characters as new_text has are replaced.

• Returns: STRING (BYTES when text is BYTES: then it counts bytes).
• Notes: overlay('Txxxxas' PLACING 'hom' FROM 2 FOR 4) is Thomas. FOR 0 inserts without removing anything. A start past the end adds at the end.
• Errors: a start of 0 or less: "negative substring length not allowed" (SQLSTATE 22011), as PostgreSQL.

Example

SELECT overlay('Txxxxas' PLACING 'hom' FROM 2 FOR 4) AS thomas,
       overlay('98XXXXXX45' PLACING '******' FROM 3) AS masked,
       overlay('abc' PLACING '-' FROM 2 FOR 0) AS inserted;

Result:

(1 row)

normalize

Syntax:

normalize(text)
normalize(text, NFC | NFD | NFKC | NFKD)

Brings text into one Unicode normal form. The same letter can be written two ways: á as one character, or as a followed by an accent mark. They look the same but do not compare equal until both are normalized.

• Returns: STRING.
• Notes: the form is written as a bare word, as in PostgreSQL; 'NFC' in quotes works too. Text of plain English letters comes back at once, unchanged.
• Errors: another form name: "invalid normalization form" (SQLSTATE 22023).

Example

SELECT normalize(U&'\0061\0301', NFC) = U&'\00E1' AS same_letter,
       length(normalize('á', NFD)) AS letters_nfd,
       normalize('file ²', NFKC) AS plain;

Result:

(1 row)

U&'...' is PostgreSQL's way to write characters by number: \XXXX (4 hex digits) or \+XXXXXX (6), and \\ for a backslash.

to_ascii

Syntax:

to_ascii(text)
to_ascii(text, 'LATIN1')

The text with accents taken off, in plain ASCII: to_ascii('Karél') is Karel.

• Returns: STRING.
• Notes: Latin-1 letters follow PostgreSQL's table (Æ → A, ß → B, ÿ → y). Other letters with accents keep their letter (ě → e). A PostgreSQL database in UTF-8 refuses to_ascii altogether; KDB answers as a PostgreSQL database in LATIN1 does.
• Errors: a character with no ASCII form, such as Nepali letters: "has no ASCII equivalent" (SQLSTATE 22P05). Encodings other than LATIN1 are not supported (0A000).

Example

SELECT to_ascii('Karél') AS a, to_ascii('Škoda Ørsted') AS b;

Result:

(1 row)

bit_length

Syntax:

bit_length(text)

The size of the text in bits: 8 times octet_length. bit_length('jose') is 32; bit_length('न') is 24.

• Returns: LONG. Works on BYTES too.

Example

SELECT bit_length('jose') AS english, bit_length('न') AS nepali_letter;

Result:

(1 row)

to_hex

Syntax:

to_hex(number)

A whole number in hexadecimal (base 16), small letters: to_hex(255) is ff.

• Returns: STRING.
• Notes: a negative number is written as PostgreSQL does: in 32 bits when it fits an INT (to_hex(-1) is ffffffff), else in 64 bits.

Example

SELECT to_hex(255) AS ff, to_hex(4096) AS big, to_hex(-1) AS minus_one;

Result:

(1 row)

parse_ident

Syntax:

parse_ident(name)
parse_ident(name, strict)

Splits a qualified name like "SomeSchema".someTable into its parts, the PostgreSQL way: quotes are taken off, a name without quotes is made lower case, "" inside quotes is one quote.

• Returns: an array of STRING (a JSON list).
• Notes: with strict = false, text after the name is ignored: parse_ident('a b', false) is {a}.
• Errors: text that is not a valid name ("string is not a valid identifier", SQLSTATE 22023).

Example

SELECT parse_ident('"SomeSchema".someTable') AS parts,
       parse_ident('market.Trades')[2] AS table_part;

Result:

(1 row)

to_char for numbers

Syntax:

to_char(number, format)

Writes a number as text with a picture of the digits, exactly as PostgreSQL does. (For times see to_char.)

• Returns: STRING. Without FM the text starts with a place for the sign: to_char(485, '999') is 485.
• Notes: a number with more digits than the picture has places shows # in every place. A DECIMAL (and a number written in the query, such as 1234.5) is exact and rounds halves away from zero; a DOUBLE rounds as PostgreSQL's float8 does (at most 15 digits). A whole number with a time format ('YYYY-MM-DD') is still read as milliseconds since 1970, as before. A format written in the query is read once for all rows.
• Errors: PostgreSQL's (SQLSTATE 42601): "multiple decimal points", "cannot use "S" twice", ...

Example

SELECT to_char(1234.5, 'FM999,999.00') AS money,
       to_char(-485, '999PR') AS bracket,
       to_char(7, 'FM000') AS padded,
       to_char(0.0004859, '9.99EEEE') AS sci,
       to_char(2026, 'FMRN') AS roman,
       to_char(22, 'FM99th') AS ordinal;

Result:

(1 row)

Example: Turnover in a report (sample data: sample)

SELECT symbol, to_char(sum(close * volume), 'FM999,999,999,990.00') AS turnover
FROM ochl_day
GROUP BY symbol
ORDER BY symbol
LIMIT 3;

Result:

(3 rows)