Quick BI Built-in Text Functions Overview - Quick BI

Text functions are a class of functions used in data analytics to process text strings. They allow you to extract substrings, concatenate strings, and change character case. This topic describes how to use the built-in text functions.

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 specified string. substring: The substring to find.
Definition	Returns "true" if the string contains the substring. Otherwise, it returns "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, it returns "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 index position of the substring within the string, starting from the specified <start> index. The <start> parameter is optional. If the substring is not found, it returns 0. The position of the first character in the string is 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 beginning 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 specified <number> of characters from the left side of 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 string whose length you want to find.
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 string from which to remove leading spaces.
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 returns a specific number of characters from a string, 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 for character extraction. The position of the first character in the string is 1. [length]: The number of characters to return.
Definition	Returns a substring from <string> starting at the specified <start> position. The position of the first character in the string is 1. If the optional <length> parameter is included, the returned string contains only that number of 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 string containing characters to be replaced. substring: The specified substring. replacement: The new string to replace the substring.
Definition	Searches for <substring> in <string> and replaces it with <replacement>. If <substring> is not found, the string remains unchanged.
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 specified <number> of characters from the right side of 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 string from which to remove trailing spaces.
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 separates fields.

Syntax	BI_SPLIT(string, delimiter, token number)
Parameter description	string: The string to split. delimiter: The separator. token number: The Nth token to return. A positive number counts from the left (for example, 1 is the first token from the left). A negative number counts from the right (for example, -1 is the first token from the right).
Definition	Splits <string> into multiple substrings using the delimiter and returns one of them.
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: A user-specified string. substring: The substring to find.
Definition	Returns "true" if <string> starts with <substring>. Otherwise, it returns "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 string from which to remove leading and trailing spaces.
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'