Skip to main content

Text Functions

concat​

concat(value1, value2, ...)
Examples
concat('Hello', ' ', 'World') // -> Hello World
concat('Hello', ' ', 'World', '!') // -> Hello World!

Description

This function concatenates multiple strings into a single string.

Return type

Text

find​

find(text, substring)

Description

Returns the position of the first occurrence of a substring within a text string. The returned position is a positive number starting from 1. Returns 0 if the substring is not found.

Aliases

find_index

Return type

Number

Examples

Find the position of 'World' in 'Hello World'
find('Hello World', 'World')  // Returns 7
Find the position of 'o' in 'Hello World'
find('Hello World', 'o')  // Returns 5
Find the position of 'Universe' in 'Hello World'
find('Hello World', 'Universe')  // Returns 0

left​

left(text, length)

Description

Returns the leftmost characters of a text string, up to the specified length.

Return type

Text

Examples

Get the first 5 characters of 'Hello World'
left('Hello World', 5)  // Returns 'Hello'
Get the first 10 characters of 'Hello'
left('Hello', 10)  // Returns 'Hello'
right(text, length)

Description

Returns the rightmost characters of a text string, up to the specified length.

Return type

Text

Examples

Get the last 5 characters of 'Hello World'
right('Hello World', 5)  // Returns 'World'
Get the last 10 characters of 'Hello'
right('Hello', 10)  // Returns 'Hello'

mid​

mid(text, start, length)

Description

Extracts a substring of a specified length from a text string, starting at a given position.

  • text: the text string
  • start: the position where the extraction starts. It must be a positive number. The first position in the text is 1.
  • length: the (maximum) length of the substring

Return type

Text

Examples

Extract 'World' from 'Hello World'
mid('Hello World', 7, 5)  // Returns 'World'
Extract 'llo' from 'Hello World' starting from position 3
mid('Hello World', 3, 3)  // Returns 'llo'

len​

len(text)

Description

Returns the length of a text string (number of characters).

Return type

Number

Examples

Get the length of 'Hello World'
len('Hello World')  // Returns 11
Get the length of an empty string
len('')  // Returns 0

lpad​

lpad(text, length, pad_string)

Description

Pads the left side of a text string with a specified pad string until it reaches the specified length. If the text is already longer than the length it will be truncated.

Return type

Text

Examples

Pad 'Hello' to length 10 with ' '
lpad('Hello', 10, ' ')  // Returns '     Hello'
Pad 'Hello' to length 10 with 'abc'
lpad('Hello', 10, 'abc')  // Returns 'abcabHello'
Pad 'Hello World' to length 10 with '0'
lpad('Hello World', 10, '0')  // Returns 'Hello Worl'

rpad​

rpad(text, length, pad_string)

Description

Pads the right side of a text string with a specified pad string until it reaches the specified length. If the text is already longer than the length it will be truncated.

Return type

Text

Examples

Pad 'Hello' to length 10 with ' '
rpad('Hello', 10, ' ')  // Returns 'Hello     '
Pad 'Hello' to length 10 with 'abc'
rpad('Hello', 10, 'abc')  // Returns 'Helloabcab'
Pad 'Hello World' to length 10 with '0'
rpad('Hello World', 10, '0')  // Returns 'Hello Worl'

lower​

lower(text)

Description

Converts a text string to lowercase.

Return type

Text

Examples

Convert 'Hello World' to lowercase
lower('Hello World')  // Returns 'hello world'
Convert 'HELLO' to lowercase
lower('HELLO')  // Returns 'hello'

upper​

upper(text)

Description

Converts a text string to uppercase.

Return type

Text

Examples

Convert 'Hello World' to uppercase
upper('Hello World')  // Returns 'HELLO WORLD'
Convert 'hello' to uppercase
upper('hello')  // Returns 'HELLO'

trim​

trim(text)

Description

Removes leading and trailing whitespace from a text string.

Return type

Text

Examples

Trim whitespace from ' Hello World '
trim('  Hello World  ')  // Returns 'Hello World'
Trim whitespace from ' Hello '
trim(' Hello ')  // Returns 'Hello'

ltrim​

ltrim(text)

Description

Removes leading whitespace from a text string.

Return type

Text

Examples

Trim leading whitespace from ' Hello World'
ltrim('  Hello World')  // Returns 'Hello World'
Trim leading whitespace from ' Hello '
ltrim(' Hello ')  // Returns 'Hello '

rtrim​

rtrim(text)

Description

Removes trailing whitespace from a text string.

Return type

Text

Examples

Trim trailing whitespace from 'Hello World '
rtrim('Hello World  ')  // Returns 'Hello World'
Trim trailing whitespace from ' Hello '
rtrim(' Hello ')  // Returns ' Hello'

regexp_extract​

regexp_extract(text, regex, [occurrence], [group: _group], [flags: _flags])

Description

Extracts a substring from a text string that matches a regular expression pattern.

Return type

Text

Examples

Extract a number from a string
regexp_extract('Product123', '[0-9]+')  // Returns '123'
Extract the second word from a string
regexp_extract('Hello World Example', '\\w+', 2) // Returns 'World'
Extract text with case-insensitive match
regexp_extract('Hello World', 'hello', flags: 'i') // Returns 'Hello'
Extract a substring with a capture group
regexp_extract('Product123.3', '(\\d+)\\.\\d+', group: 1) // Returns '123'

Parameters

  • text: The text string to search within
  • regex: The regular expression pattern to match (exact regex syntax depends on the database)
  • occurrence (optional): The position of the occurrence to return. E.g. 1 means return the first occurrence that matches the regex.
  • group (optional): The capture group to extract from the matched occurrence (BigQuery does not support this)
  • flags (optional): Flags to modify the behavior of the regular expression matching (supported flags depend on the database)

Notes

This function is not supported in the following databases:

  • SQL Server

For BigQuery, the group parameter is not supported. BigQuery will automatically extract the first capture group if one exists in the regex. BigQuery will throw an error if the regex has multiple capture groups. To use grouping without extracting a specific group, utilize non-capture groups.

regexp_match​

regexp_match(text, regex, [flags: _flags])

Description

Checks if a text string matches a regular expression pattern.

Return type

Truefalse

Examples

Check if a string contains a number
regexp_match('Product123', '[0-9]+')  // Returns true
Check if a string starts with 'hello' (case insensitive)
regexp_match('Hello World', '^hello', flags: 'i')  // Returns true

Parameters

  • text: The text string to search within
  • regex: The regular expression pattern to match (exact regex syntax depends on the database)
  • flags (optional): Flags to modify the behavior of the regular expression matching (supported flags depend on the database)

Notes

This function is not supported in the following databases:

  • SQL Server

regexp_replace​

regexp_replace(text, regex, substitute, [flags: _flags])

Description

Replaces substrings in a text that match a regular expression pattern with a specified replacement text.

Return type

Text

Examples

Replace all numbers in a text with 'X'
regexp_replace('Product123', '[0-9]+', 'X')  // Returns 'ProductX'
Remove redundant whitespace from a text
regexp_replace('Hello   World', '\\s+', ' ')  // Returns 'Hello World'
Swap the first and last name
regexp_replace('John Doe', '(\\w+) (\\w+)', '\\2, \\1')  // Returns 'Doe, John'

Parameters

  • text: The text string to perform the replacement in
  • regex: The regular expression pattern to match (exact regex syntax depends on the database)
  • substitute: The replacement string. You can use backreferences like \\1 or $1 (depending on the specific database) to refer to captured groups in the regex
  • flags (optional): Flags to modify the behavior of the regular expression matching (supported flags depend on the database)

Notes

This function is not supported in the following databases:

  • SQL Server

replace​

replace(text, old_substring, new_substring)

Description

Replaces all occurrences of a substring within a text string with a new substring.

Return type

Text

Examples

Replace 'World' with 'Universe' in 'Hello World'
replace('Hello World', 'World', 'Universe')  // Returns 'Hello Universe'
Replace all 'o' with '0' in 'Hello World'
replace('Hello World', 'o', '0')  // Returns 'Hell0 W0rld'

split_part​

split_part(text, delimiter, part_number)

Description

Splits a text string into parts based on a delimiter and returns the specified part. part_number is the number of the part to return, starting from 1. It must be a positive number.

Return type

Text

Examples

Get the second part of 'apple,banana,cherry' split by ','
split_part('apple,banana,cherry', ',', 2)  // Returns 'banana'
Get the first part of 'Hello World' split by ' '
split_part('Hello World', ' ', 1)  // Returns 'Hello'

Notes

This function is not supported in the following databases:

  • Microsoft SQL Server
Let us know what you think about this document :)