Text Functions - Quick BI - Alibaba Cloud Documentation Center

Text functions are a class of functions used to process text strings in data analysis. They can help you extract substrings, concatenate strings, and convert case. This topic describes how to use text functions in built-in functions.

BI_CONCAT

BI_CONCAT is used to combine multiple strings into one string.

Syntax	BI_CONCAT(string1, string2, …)
Description	string1, string2, ...: multiple strings to be combined into one string.
Description	Combines multiple strings into one string.
Output data type	String
Examples	BI_CONCAT('a', '-', 'b') = 'a-b'

BI_CONTAINS

BI_CONTAINS is used to determine whether a given string contains a specified substring.

Syntax	BI_CONTAINS(string, substring)
Description	string: the given string. substring: specifies a substring.
Description	Returns true if the given string contains the specified substring.
Output data type	Boolean
Examples	BI_CONTAINS('Hello World', ' ') = true

BI_ENDWITH

BI_ENDWITH is used to determine whether a given string ends with a specified substring.

Syntax	BI_ENDWITH(string, substring)
Description	string: the given string. substring: specifies a substring.
Description	Returns true if <string> ends with <substring>. Trailing spaces are ignored.
Output data type	Boolean
Examples	BI_ENDWITH('Hello World', 'World') = true

BI_FIND

BI_FIND is used to return the position of a character.

Syntax	BI_FIND(substring, string, [start])
Description	substring: the string to be searched. string: the string to be searched. [start]: Specifies the index position at which the search character is performed.
Description	Returns the index position of the substring in the string. The index starts from the specified <start> (<start> optionally). If the substring is not found, 0 is returned. The position of the first character in the string is 1.
Output data type	N/A
Examples	BI_FIND('World', 'Hello World') =7 BI_FIND('Computer', 'Hello World') = 0

BI_LEFT

BI_LEFT is used to return the first few characters in a string based on a specified number of characters.

Syntax	BI_LEFT(string, number)
Description	string: the string of characters to be selected. number: specifies the length of the returned string.
Description	Returns the leftmost <number> character of a string.
Output data type	String
Examples	BI_LEFT('Hello World', 5) = 'Hello'

BI_LEN

BI_LEN is used to return the length of a string.

Syntax	BI_LEN(string)
Description	string: the string whose length is to be returned.
Description	Returns the length of a string.
Output data type	N/A
Examples	BI_LEN('Hello World') = 11

BI_LOWER

BI_LOWER is used to convert all characters in a string to lowercase.

Syntax	BI_LOWER(string)
Description	string: a string that needs to be converted to lowercase.
Description	Returns a <string> with all lowercase characters.
Output data type	String
Examples	BI_UPPER('Hello World') = 'hello world'

BI_LTRIM

BI_LTRIM is used to remove leading spaces from a string.

Syntax	BI_LTRIM(string)
Description	string: the string from which prepended spaces are to be removed.
Description	Removes all leading spaces of the <string>.
Output data type	String
Examples	BI_LTRIM(' Hello World ') = 'Hello World '

BI_MID

BI_MID is used to return the string at the specified position.

Syntax	BI_MID(string, start, [length])
Description	string: the string of characters to be selected. start: the start position of the characters to be extracted from the text. The position of the first character in the string is 1. [length]: the length of the returned string.
Description	Returns the <string> starting at the specified <start> position. The position of the first character in the string is 1. If the optional numeric parameter <length> is added, the returned string contains only that number of characters.
Output data type	String
Examples	BI_MID('Hello World', 2) = 'ello World' BI_MID('Hello World', 2, 4) ='ello'

BI_REPLACE

BI_REPLACE is used to replace characters at specified positions.

Syntax	BI_REPLACE(string, substring, replacement)
Description	string: the string in which some characters need to be replaced. substring: the specified string. replacement: a new character that is used to replace some characters.
Description	Search for <substring> in <string> and replace it with <replacement>. If <substring> is not found, the string remains unchanged.
Output data type	String
Examples	BI_REPLACE('Hello World', ' ', '-') = 'Hello-World'

BI_RIGHT

BI_RIGHT is used to return the last few characters in a string based on a specified number of characters.

Syntax	BI_RIGHT(string, number)
Description	string: the string of characters to be selected. number: specifies the length of the returned string.
Description	Returns the rightmost <number> character of a string.
Output data type	String
Examples	BI_RIGHT('Hello World', 5) = 'World'

BI_RTRIM

BI_RTRIM is used to remove trailing spaces from a string.

Syntax	BI_RTRIM(string)
Description	string: the string from which trailing spaces are to be removed.
Description	Removes all trailing spaces from the <string>.
Output data type	String
Examples	BI_RTRIM(' Hello World ') = ' Hello World'

BI_SPLIT

BI_SPLIT is used to delimit fields.

Syntax	BI_SPLIT(string, delimiter, token number)
Description	string: the string to be separated. delimiter: the delimiter. token number: returns the N-th character. The positive number is the number from left to right (for example, 1 is the first on the left), and the negative number is the number from right to left (for example, -1 is the first on the right).
Description	Use a delimiter to divide a <string> into multiple substrings and return one of them.
Output data type	String
Examples	BI_SPLIT ('a-b-c-d', '-', 2) = 'b' BI_SPLIT ('a\|b\|c\|d', '\|', -2) = 'c'
Limits	Not supported for SQL Server data sources.

BI_STARTWITH

BI_STARTWITH is used to determine whether a given string starts with a specified substring.

Syntax	BI_STARTWITH(string, substring)
Description	string: the given string. substring: specifies a substring.
Description	Returns true if <string> starts with <substring>. Prepended spaces are ignored.
Output data type	Boolean
Examples	BI_STARTWITH('Hello World, 'Hello') = true

BI_TRIM

BI_TRIM is used to remove the leading and trailing spaces of a string.

Syntax	BI_TRIM(string)
Description	string: the string whose leading and trailing spaces are to be removed.
Description	Removes all leading and trailing spaces of the <string>.
Output data type	String
Examples	BI_TRIM(' Hello World ') = 'Hello World'

BI_UPPER

BI_UPPER is used to convert all characters in a string to uppercase.

Syntax	BI_UPPER(string)
Description	string: the string to be converted to uppercase.
Description	Returns a <string> with all uppercase characters.
Output data type	String
Examples	BI_UPPER('Hello World') = 'HELLO WORLD'