String Functions

This page is a quick reference for the string functions in NocoDB formula fields. Each function has a short description, its syntax and a sample.

Ask NocoAI

Copy a prompt, paste it into NocoAI in any base, and replace the words in [brackets] with your own.

Add a formula field that joins [First Name] and [Last Name] with a space
Add a formula field that shows [Email] in lowercase
Add a formula field that shows the first [3] characters of [Code]

CONCAT

The CONCAT function joins one or more strings into one string.

Syntax

CONCAT(text, [text,...])

Sample

CONCAT('John', ' ', 'Doe') => 'John Doe'

LEFT

The LEFT function returns the first 'n' characters of the input string.

Syntax

LEFT(text, count)

Sample

LEFT('123-456-7890', 3) => '123'

LEN

The LEN function returns the total number of characters in the string.

Syntax

LEN(text)

Sample

LEN('Product Description') => 19

LOWER

The LOWER function changes all characters in the input string to lowercase.

Syntax

LOWER(text)

Sample

LOWER('User INPUT') => 'user input'

MID

The MID function returns a substring of the input string. The substring starts at the position and has the count of characters.

Syntax

MID(text, position, [count])

Sample

MID('This is a sentence', 5, 3) => 'is '

REGEX_EXTRACT

The REGEX_EXTRACT function finds the first match of the regular expression pattern in the input string. It returns the matched substring.

Syntax

REGEX_EXTRACT(text, pattern)

Sample

REGEX_EXTRACT('Error: Something went wrong', 'Error: (.*)') => 'Something went wrong'

REGEX_MATCH

The REGEX_MATCH function checks if the input string matches the regular expression pattern. It returns 1 if there is a match and 0 if there is no match.

Syntax

REGEX_MATCH(text, pattern)

Sample

REGEX_MATCH('123-45-6789', '\d{3}-\d{2}-\d{4}') => 1

REGEX_REPLACE

The REGEX_REPLACE function finds all matches of the regular expression pattern in the input string. It replaces each match with the replacement string.

Syntax

REGEX_REPLACE(text, pattern, replacer)

Sample

REGEX_REPLACE('Replace all bugs', 'bug', 'feature') => 'Replace all features'

REPEAT

The REPEAT function repeats the string the given number of times. Use it to make repeated patterns or sequences.

Syntax

REPEAT(text, count)

Sample

REPEAT('😃', 3) => '😃😃😃'

REPLACE

The REPLACE function finds all instances of a substring in the string. It replaces each instance with another substring.

Syntax

REPLACE(text, srchStr, rplcStr)

Sample

REPLACE('Replace old text', 'old', 'new') => 'Replace new text'

RIGHT

The RIGHT function returns the last 'n' characters of the input string. Use it to get a substring from the right.

Syntax

RIGHT(text, n)

Sample

RIGHT('file_name.txt', 3) => 'txt'

SEARCH

The SEARCH function finds the position of the substring in the input string. It returns the index if it finds the substring, and 0 if not.

Syntax

SEARCH(text, srchStr)

Sample

SEARCH('user@example.com', '@') => 5

SUBSTR

The SUBSTR function returns a substring of the input string. The substring starts at the position. The count of characters is optional.

Syntax

SUBSTR(text, position, [count])

Sample

SUBSTR('Extract this text', 9, 4) => 'this'

TRIM

The TRIM function removes all leading and trailing whitespace from the input string.

Syntax

TRIM(text)

Sample

TRIM('   Trim this   ') => 'Trim this'

UPPER

The UPPER function changes all characters in the input string to uppercase.

Syntax

UPPER(text)

Sample

UPPER('title') => 'TITLE'

URL

The URL function checks if the input string is a valid URL. It changes the string into a hyperlink.

Syntax

URL(text)

Sample

URL('https://www.example.com') => a clickable link for https://www.example.com

URLENCODE

The URLENCODE function percent-encodes special characters in a string. You can then put the string into a URL as a query parameter.

It is similar to the JavaScript encodeURIComponent() function, with one difference. It encodes only these characters:

Characters with a special meaning in RFC 3986 section 2.2
Percent signs
Spaces

It does not encode other characters, for example letters from non-Latin alphabets. Like encodeURIComponent(), use it only to encode URL components, not whole URLs.

Syntax

URLENCODE(text)

Sample

'https://example.com/q?param=' & URLENCODE('Hello, world')
=> 'https://example.com/q?param=Hello%2C%20world'

MD5

The MD5 function returns the MD5 hash of the input value as a 32-character lowercase hexadecimal string. Use it for checksums and to verify data integrity.

Syntax

MD5(value)

Sample

MD5('nocodb') => 'd867ba3554696af54e71a5b42ccd4947'

SHA256

The SHA256 function returns the SHA-256 hash of the input value as a 64-character lowercase hexadecimal string. Use it for checksums and to verify data integrity.

Syntax

SHA256(value)

Sample

SHA256('nocodb') => a 64-character hexadecimal string

SHA512

The SHA512 function returns the SHA-512 hash of the input value as a 128-character lowercase hexadecimal string. Use it for checksums and to verify data integrity.

Syntax

SHA512(value)

Sample

SHA512('nocodb') => a 128-character hexadecimal string

The MD5, SHA256 and SHA512 functions are available only on PostgreSQL and MySQL/MariaDB (including NocoDB Cloud, which runs on PostgreSQL). They are not available on SQLite, SQL Server, Oracle, Snowflake or Databricks, and are hidden from the formula function list on those databases.

ISBLANK

The ISBLANK function checks if the input is empty or null. It returns FALSE if the input has a value, and TRUE if not.

Syntax

ISBLANK(text)

Sample

ISBLANK('') => true
ISBLANK('Hello') => false

ISNOTBLANK

The ISNOTBLANK function checks if the input is not empty or null. It returns TRUE if the input has a value, and FALSE if not.

Syntax

ISNOTBLANK(text)

Sample

ISNOTBLANK('') => false
ISNOTBLANK('Hello') => true