Quick BI text functions - string manipulation reference - Quick BI

Text functions process text strings in data analytics. Use them to extract substrings, concatenate strings, and change character case.

BI_CONCAT

BI_CONCAT merges multiple strings into a single string.

Syntax	BI_CONCAT(string1, string2, …)
Parameter description	string1, string2, ...: The strings to merge.
Definition	Merges multiple strings into a single string.
Output	String
Example	BI_CONCAT('a', '-', 'b') = 'a-b'

BI_CONTAINS

BI_CONTAINS checks if a string contains a specified substring.

Syntax	BI_CONTAINS(string, substring)
Parameter description	string: The input string. substring: The substring to find.
Definition	Returns "true" if the string contains the substring, otherwise "false".
Output	String
Example	BI_CONTAINS("Hello World", " ") = "true"

BI_ENDWITH

BI_ENDWITH checks if a string ends with a specified substring.

Syntax	BI_ENDWITH(string, substring)
Parameter description	string: The string to check. substring: The substring to find.
Definition	Returns "true" if <string> ends with <substring>, otherwise "false". Trailing spaces are ignored.
Output	String
Example	BI_ENDWITH("Hello World", "World") = "true"

BI_FIND

BI_FIND returns the position of a substring.

Syntax	BI_FIND(substring, string, [start])
Parameter description	substring: The string to find. string: The string to search within. [start]: The index position to start the search from.
Definition	Returns the position of the substring within the string, starting from <start> (optional). Returns 0 if not found. Position index starts at 1.
Output	Number
Example	BI_FIND('World', 'Hello World') =7 BI_FIND('Computer', 'Hello World') = 0

BI_LEFT

BI_LEFT returns a specified number of characters from the start of a string.

Syntax	BI_LEFT(string, number)
Parameter description	string: The string from which to extract characters. number: The number of characters to return.
Definition	Returns the first <number> characters from the string.
Output	String
Example	BI_LEFT('Hello World', 5) = 'Hello'

BI_LEN

BI_LEN returns the length of a string.

Syntax	BI_LEN(string)
Parameter description	string: The input string.
Definition	Returns the length of the string.
Output	Number
Example	BI_LEN('Hello World') = 11

BI_LOWER

BI_LOWER converts all characters in a string to lowercase.

Syntax	BI_LOWER(string)
Parameter description	string: The string to convert to lowercase.
Definition	Returns <string> with all characters in lowercase.
Output	String
Example	BI_UPPER('Hello World') returns 'hello world'.

BI_LTRIM

BI_LTRIM removes leading spaces from a string.

Syntax	BI_LTRIM(string)
Parameter description	string: The input string.
Definition	Removes all leading spaces from <string>.
Output	String
Example	BI_LTRIM(' Hello World ') = 'Hello World '
Limits	Not supported by Lindorm (LindormTSDB) data sources.

BI_MID

BI_MID extracts a substring starting at a specified position.

Syntax	BI_MID(string, start, [length])
Parameter description	string: The string from which to extract characters. start: The starting position (1-based index). [length]: The number of characters to return.
Definition	Returns a substring from <string> starting at <start> (1-based index). If <length> is specified, returns only that many characters.
Output	String
Example	BI_MID('Hello World', 2) = 'ello World' BI_MID('Hello World', 2, 4) ='ello'
Limits	Not supported by TiDB data sources.

BI_REPLACE

BI_REPLACE replaces characters at a specified position.

Syntax	BI_REPLACE(string, substring, replacement)
Parameter description	string: The source string. substring: The specified substring. replacement: The replacement string.
Definition	Replaces <substring> in <string> with <replacement>. Returns the original string if <substring> is not found.
Output	String
Example	BI_REPLACE('Hello World', ' ', '-') = 'Hello-World'

BI_RIGHT

BI_RIGHT returns a specified number of characters from the end of a string.

Syntax	BI_RIGHT(string, number)
Parameter description	string: The string from which to extract characters. number: The number of characters to return.
Definition	Returns the last <number> characters from the string.
Output	String
Example	BI_RIGHT('Hello World', 5) = 'World'

BI_RTRIM

BI_RTRIM removes trailing spaces from a string.

Syntax	BI_RTRIM(string)
Parameter description	string: The input string.
Definition	Removes all trailing spaces from <string>.
Output	String
Example	BI_RTRIM(' Hello World ') = ' Hello World'
Limits	Not supported by Lindorm (LindormTSDB) data sources.

BI_SPLIT

BI_SPLIT splits a string by a delimiter and returns a specified token.

Syntax	BI_SPLIT(string, delimiter, token number)
Parameter description	string: The string to split. delimiter: The separator. token number: The Nth token to return. Positive counts from the left (1 = first). Negative counts from the right (-1 = last).
Definition	Splits <string> by the delimiter and returns the token at the specified position.
Output	String
Example	BI_SPLIT ('a-b-c-d', '-', 2) = 'b' BI_SPLIT ('a\|b\|c\|d', '\|', -2) = 'c'
Limits	Not supported by SQL Server, Lindorm, Elasticsearch, IBM DB2 LUW, or SAP IQ (Sybase IQ) data sources. Negative numbers are not supported for HANA data sources.

BI_STARTWITH

BI_STARTWITH checks if a string starts with a specified substring.

Syntax	BI_STARTWITH(string, substring)
Parameter description	string: The input string. substring: The substring to find.
Definition	Returns "true" if <string> starts with <substring>, otherwise "false". Leading spaces are ignored.
Output	String
Example	BI_STARTWITH("Hello World", "Hello") = "true"

BI_TRIM

BI_TRIM removes leading and trailing spaces from a string.

Syntax	BI_TRIM(string)
Parameter description	string: The input string.
Definition	Removes all leading and trailing spaces from <string>.
Output	String
Example	BI_TRIM(' Hello World ') = 'Hello World'

BI_UPPER

BI_UPPER converts all characters in a string to uppercase.

Syntax	BI_UPPER(string)
Parameter description	string: The string to convert to uppercase.
Definition	Returns <string> with all characters in uppercase.
Output	String
Example	BI_UPPER('Hello World') = 'HELLO WORLD'