IoT Platform:String functions

ASCII

• Syntax

  bigint ascii(string <str>)

• Description

  Returns the ASCII code of the first character in a string that is specified by str.

• Parameters

  str: required. A value of the STRING type. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation.

• Return value

  A value of the BIGINT type is returned. The return value varies based on the values of the input parameters.

  • If the value of str is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.

  • If the value of str is null, null is returned.

• Examples

  • Example 1: Return the ASCII code of the first character in the string abcde. Sample statement:

    -- The return value is 97. 
    select ascii('abcde'); 

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select ascii(null);

CHAR_MATCHCOUNT

• Syntax

  bigint char_matchcount(string <str1>, string <str2>)

• Description

  Returns the number of characters that belong to str1 and appear in str2.

• Parameters

  str1 and str2: required. Values of the STRING type. The values must be valid UTF-8 strings. If invalid characters (non-Unicode-encoded characters) are found during the comparison of the two strings, this function returns a negative value.

• Return value

  A value of the BIGINT type is returned. If the value of str1 or str2 is null, null is returned.

• Examples

  • Example 1: Calculate the number of characters that belong to the string aabc and appear in the string abcde. Sample statement:

    -- The return value is 4. 
    select char_matchcount('aabc','abcde');

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select char_matchcount(null,'abcde');

CHR

• Syntax

  string chr(bigint <ascii>)

• Description

  Converts a specified ASCII code to a character.

• Parameters

  ascii: required. An ASCII code of the BIGINT type. Valid values: 0 to 128. If the input value is of the STRING, DOUBLE, or DECIMAL type, the value is implicitly converted to a value of the BIGINT type before calculation.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If the value of ascii is not within the valid range, an error is returned.

  • If the value of ascii is not of the BIGINT, STRING, DOUBLE, or DECIMAL type, an error is returned.

  • If the value of ascii is null, null is returned.

• Examples

  • Example 1: Convert the ASCII code 100 to characters. Sample statement:

    -- The return value is d. 
    select chr(100);

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select chr(null);

  • Example 3: The input value is a character of the STRING type. Sample statement:

    -- The input value is implicitly converted to a value of the BIGINT type before calculation, and value d is returned. 
    select chr('100');

CONCAT

• Syntax

  string concat(string <str1>, string <str2>[,...])

• Description

  Concatenates multiple strings and returns a new string.

• Parameters

  • a and b: required. These parameters specify arrays. that T in array<T> specifies the data type of the elements in the arrays. The elements can be of any data type. The elements in Array a and the elements in Array b must be of the same data type. The null elements are also involved in the operation.

  • str1 and str2: required. Values of the STRING type. If the input values are of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the values are implicitly converted to values of the STRING type before calculation. If the input values are of other data types, an error is returned.

• Return values

  A value of the STRING type is returned. If no input parameters are configured or an input parameter is set to null, null is returned.

• Examples

  • Example 1: Concatenate the strings aabc and abcde. Sample statement:

    -- The return value is aabcabcde. 
    select concat('aabc','abcde');

  • Example 2: The input value is empty. Sample statement:

    -- The return value is null. 
    select concat();

  • Example 3: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select concat('aabc', 'abcde', null);

CONCAT_WS

• Syntax

  string concat_ws(string <separator>, string <str1>, string <str2>[,...])

• Description

  Concatenates all input strings or array elements by using a specified delimiter and returns the result.

• Parameters

  • separator: required. A delimiter of the STRING type.

  • str1 and str2: At least two strings must be specified. The values are of the STRING type. If the input value is of the BIGINT, DECIMAL, DOUBLE, or DATETIME type, the value is implicitly converted to values of the STRING type before calculation.

• Return value

  A value of the STRING or STRUCT type is returned. The return value varies based on the following rules:

  • If the value of str1 or str2 is not of the STRING, BIGINT, DECIMAL, DOUBLE, or DATETIME type, an error is returned.

  • If no input parameters are available or an input parameter is set to null, null is returned.

• Examples

  • Example 1: Concatenate the strings name and hanmeimei by using a colon (:). Sample statement:

    -- The return value is name:hanmeimei. 
    select concat_ws(':','name','hanmeimei');

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select concat_ws(':','avg',null,'34');

FIND_IN_SET

• Syntax

  bigint find_in_set(string <str1>, string <str2>)

• Description

  Returns the position of the substring str1 in string str2. The substrings in the string str2 are separated by commas (,). The first position is 1.

• Parameters

  • str1: required. A value of the STRING type. This parameter specifies the substring whose position you want to obtain.

  • str2: required. A value of the STRING type. This parameter specifies a string in which substrings are separated by commas (,).

• Return value

  A value of the BIGINT type is returned. The return value varies based on the values of the input parameters.

  • If str2 does not contain str1 or str1 contains commas (,), 0 is returned.

  • If the value of str1 or str2 is null, null is returned.

• Examples

  • Example 1: Return the position of the substring ab in the string abc,hello,ab,c. Sample statement:

    -- The return value is 3. 
    select find_in_set('ab', 'abc,hello,ab,c');

  • Example 2: Return the position of the substring hi in the string abc,hello,ab,c. Sample statement:

    -- The return value is 0. 
    select find_in_set('hi', 'abc,hello,ab,c');

  • Example 3: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select find_in_set(null, 'abc,hello,ab,c');

FORMAT_NUMBER

• Syntax

  string format_number(float|double|decimal <expr1>, int <expr2>)

• Description

  Formats expr1 as a string with the format specified by expr2.

• Parameters

  • expr1: required. A value of the FLOAT, DOUBLE, or DECIMAL type. This parameter specifies the expression that you want to format.

  • expr2: required. A value of the INT type. Valid values: 0 to 340. This parameter specifies the number of decimal places that you want to retain. This parameter can also be expressed in the #,###,###.## format. The number of decimal places returned varies based on the value of this parameter.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If the value of expr2 is greater than 0 and is less than or equal to 340, a value that is rounded to the specified number of decimal places is returned.

  • If the value of expr2 is 0, the return value contains only the integer part and does not contain the decimal point or decimal part.

  • If the value of expr2 is less than 0 or greater than 340, an error is returned.

  • If expr1 or expr2 is empty or is set to null, null is returned.

• Examples

  • Example 1: Return a value in the specified format based on a given number. Sample statement:

    -- The return value is 5.230. 
    select format_number(5.230134523424545456,3);
    -- The return value is 12,332.123. 
    select format_number(12332.123456, '#,###,###,###.###');

  • Example 2: An input parameter is empty or set to null. Sample statement:

    -- The return value is null. 
    select format_number('',3);
    -- The return value is null. 
    select format_number(null,3);

INITCAP

• Syntax

  string initcap(<str>)

• Description

  Converts a string specified by str to title case. In the result, the first letter of each word is capitalized and the remaining letters are in lowercase. Words are delimited by spaces.

• Parameters

  str: required. A value of the STRING type. This parameter specifies the string that you want to convert.

• Return value

  A string is returned in title case: the first letter of each word is capitalized and the rest are in lowercase.

• Examples

  -- The return value is Odps Sql. 
  SELECT initcap("oDps sql");

INSTR

• Syntax

  bigint instr(string <str1>, string <str2>[, bigint <start_position>[, bigint <nth_appearance>]])

• Description

  Returns the position of the substring str2 in the string str1.

• Parameters

  • str1: required. A value of the STRING type. This parameter specifies the string that contains the substring for which you want to search. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation. If the input value is of another data type, an error is returned.

  • str2: required. A value of the STRING type. This parameter specifies the substring for which you want to search. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation. If the input value is of another data type, an error is returned.

  • start_position: optional. A value of the BIGINT type. If the input value is of another data type, an error is returned. This parameter specifies the character in str1 from which the search starts. The default start position is the first character, marked as 1. If start_position is a negative value, the search starts from the end to the start of the string and the last character is –1.

  • nth_appearance: optional. A value of the BIGINT type. The value is greater than 0. This parameter specifies the nth time that substring str2 appears in string str1. If the value of nth_appearance is of another data type or is less than or equal to 0, an error is returned.

• Return value

  A value of the BIGINT type is returned. The return value varies based on the values of the input parameters.

  • If str2 is not found in str1, 0 is returned.

  • If str2 is an empty string, this results in a successful match. For example, the value 1 is returned for select instr('abc','');.

  • If the value of str1, str2, start_position, or nth_appearance is null, null is returned.

• Examples

  • Example 1: Return the position of the substring e in the string Tech on the net. Sample statement:

    -- The return value is 2. 
    select instr('Tech on the net', 'e');

  • Example 2: Return the position of the substring on in the string Tech on the net. Sample statement:

    -- The return value is 6. 
    select instr('Tech on the net', 'on');

  • Example 3: Return the position of the second occurrence in which the substring e appears in the string Tech on the net from the third character. Sample statement:

    -- The return value is 14. 
    select instr('Tech on the net', 'e', 3, 2);

  • Example 4: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select instr('Tech on the net', null);

KEYVALUE

• Syntax

  keyvalue(string <str>,[string <split1>,string <split2>,] string <key>)
  keyvalue(string <str>,string <key>) 

• Description

  Splits the string str into key-value pairs by split1, separates the key-value pairs by split2, and then returns the value of the specified key.

• Parameters

  • str: required. A value of the STRING type. This parameter specifies the string that you want to split.

  • split1 and split2: optional. A value of the STRING type. These parameters specify the strings that are used as delimiters to split the source string. If you do not configure the two parameters, the default values are used. The default value of split1 is a semicolon (;) and the default value of split2 is a colon (:). If a key-value pair that is obtained after the source string is split by split1 contains multiple delimiters specified by split2, the returned result is undefined.

  • key: required. A value of the STRING type. After the source string is split by split1 and split2 in sequence, the value that corresponds to the key is returned.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If the value of split1 or split2 is null, null is returned.

  • If the value of str or key is null or no values matches key, null is returned.

  • If multiple key-value pairs match, the first value that matches the value of key is returned.

• Examples

  • Example 1: Split the string 0:1\;1:2 into key-value pairs and return the value that corresponds to the key 1. Sample statement:

    -- The return value is 2. 
    select keyvalue('0:1\;1:2', 1);

    The split1 and split2 parameters are not specified. The default value of split1 is a semicolon (;) and the default value of split2 is a colon (:).

    After the source string is split by split1, the key-value pairs 0:1\,1:2 are returned. After the key-value pairs are split by split2, the following keys and values are generated:

    0 1/  
    1 2

    The value 2 that corresponds to the key 1 is returned.

  • Example 2: Split the string \;decreaseStore:1\;xcard:1\;isB2C:1\;tf:21910\;cart:1\;shipping:2\;pf:0\;market:shoes\;instPayAmount:0\; into key-value pairs by using a backslash and a semicolon (\;), separate the values from keys by using a colon (:), and then return the value that corresponds to the key tf. Sample statement:

    -- The return value is 21910. 
    select keyvalue("\;decreaseStore:1\;xcard:1\;isB2C:1\;tf:21910\;cart:1\;shipping:2\;pf:0\;market:shoes\;instPayAmount:0\;","\;",":","tf");

    After the source string \;decreaseStore:1\;xcard:1\;isB2C:1\;tf:21910\;cart:1\;shipping:2\;pf:0\;market:shoes\;instPayAmount:0\; is split by using a backslash and a semicolon (\;), the following key-value pairs are generated:

    decreaseStore:1, xcard:1, isB2C:1, tf:21910, cart:1, shipping:2, pf:0, market:shoes, instPayAmount:0 

    After the key-value pairs are separated by using a colon (:), the following keys and values are generated:

    decreaseStore 1  
    xcard 1  
    isB2C 1  
    tf 21910  
    cart 1  
    shipping 2  
    pf 0  
    market shoes  
    instPayAmount 0

    The value 21910 that corresponds to the key tf is returned.

LENGTH

• Syntax

  bigint length(string <str>)

• Description

  Returns the length of the string str.

• Parameters

  str: required. A value of the STRING type. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation.

• Return value

  A value of the BIGINT type is returned. The return value varies based on the values of the input parameters.

  • If the value of str is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.

  • If the value of str is null, null is returned.

  • If the value of str is not UTF-8 encoded, -1 is returned.

• Examples

  • Example 1: Return the length of the string Tech on the net. Sample statement:

    -- The return value is 15. 
    select length('Tech on the net');

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select length(null);

LENGTHB

• Syntax

  bigint lengthb(string <str>)

• Description

  Returns the length of a string specified by str in bytes.

• Parameters

  str: required. A value of the STRING type. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation.

• Return value

  A value of the BIGINT type is returned. The return value varies based on the values of the input parameters.

  • If the value of str is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.

  • If the value of str is null, null is returned.

• Examples

  • Example 1: Return the length of the string Tech on the net in bytes. Sample statement:

    -- The return value is 15. 
    select lengthb('Tech on the net');

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select lengthb(null);

LOCATE

• Syntax

  bigint locate(string <substr>, string <str>[, bigint <start_pos>]) 

• Description

  Returns the position of the substring substr in the string str. You can use start_pos to specify the position from which the search starts. The value starts from 1.

• Parameters

  • substr: required. A value of the STRING type. This parameter specifies the substring for which you want to search.

  • str: required. A value of the STRING type. This parameter specifies the string that contains the substring for which you want to search.

  • start_pos: optional. A value of the BIGINT type. This parameter specifies the position from which the search starts.

• Return value

  A value of the BIGINT type is returned. The return value varies based on the following rules:

  • If substr cannot be found in str, 0 is returned.

  • If the value of str or substr is null, null is returned.

  • If the value of start_pos is null, 0 is returned.

• Examples

  • Example 1: Return the position of the string ab in the string abchelloabc. Sample statement:

    -- The return value is 1. 
    select locate('ab', 'abchelloabc');

  • Example 2: Return the position of the string hi in the string abchelloabc. Sample statement:

    -- The return value is 0. 
    select locate('hi', 'abc,hello,ab,c');

  • Example 3: The start_pos parameter is set to null. Sample statement:

    -- The return value is 0. 
    select locate('ab', 'abhelloabc', null);

LPAD

• Syntax

  string lpad(string <str1>, int <length>, string <str2>)

• Description

  Left pads str1 with str2 to a specified length.

• Parameters

  • str1: required. A value of the STRING type. This parameter specifies the string that you want to left pad.

  • length: required. A value of the INT type. This parameter specifies the number of characters that are used to perform left padding.

  • str2: required. This parameter specifies the string that you use to left pad another string.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If the value of length is less than the number of characters in str1, this function truncates str1 from the left to obtain a string with the number of characters specified by length.

  • If length is set to 0, an empty string is returned.

  • If no input parameters are available or an input parameter is set to null, null is returned.

• Examples

  • Example 1: Left pad the string abcdefgh with the string 12 to obtain a string with 10 characters in length. Sample statement:

    -- The return value is 12abcdefgh. 
    select lpad('abcdefgh', 10, '12');

  • Example 2: Left pad the string abcdefgh with the string 12 to obtain a string with 5 characters in length. Sample statement:

    -- The return value is abcde. 
    select lpad('abcdefgh', 5, '12');

  • Example 3: The value of length is 0. Sample statement:

    -- The return value is an empty string. 
    select lpad('abcdefgh' ,0, '12'); 

  • Example 4: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select lpad(null ,0, '12');

LTRIM

• Syntax

  string ltrim(string <str>[, <trimChars>])
  string trim(leading [<trimChars>] from <str>)

• Description

  Removes the characters from the left side of a string that is specified by str.

  • If you do not specify trimChars, the spaces on the left side are automatically removed.

  • If you specify trimChars, the substring that consists of one or more characters specified by trimChars are removed from the left side of the string that is specified by str.

• Parameters

  • str: required. A value of the STRING type. This parameter specifies the string from which the characters on the left side are removed. If the input value is of the BIGINT, DECIMAL, DOUBLE, or DATETIME type, the value is implicitly converted into a value of the STRING type before calculation.

  • trimChars: optional. A value of the STRING type. This parameter specifies the characters to be removed.

• Return value

  A value of the STRING type is returned. The return value varies based on the following rules:

  • If the value of str is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.

  • If the value of str or trimChars is null, null is returned.

• Examples

  • Example 1: Remove the space from the left side of the string yxTxyomxx . Sample statement:

    -- The return value is yxTxyomxx . 
    select ltrim(' yxTxyomxx ');
    -- The preceding statement is equivalent to the following statement: 
    select trim(leading from ' yxTxyomxx ');

  • Example 2: Remove the substring that consists of one or more characters in the xy collection from the left side of the string yxTxyomxx.

    -- The return value is Txyomxx. If x or y appears on the left side, it is removed. 
    select ltrim('yxTxyomxx', 'xy');
    -- The preceding statement is equivalent to the following statement: 
    select trim(leading 'xy' from 'yxTxyomxx');

  • Example 3: The input parameter is set to null. Sample statement:

    -- The return value is null. 
    select ltrim(null);
    select ltrim('yxTxyomxx', null);

REGEXP_COUNT

• Syntax

  bigint regexp_count(string <source>, string <pattern>[, bigint <start_position>])

• Description

  Returns the number of substrings that match a specified pattern in the source string from the start position specified by start_position.

• Parameters

  • source: required. A value of the STRING type. This parameter specifies the string that contains the substring for which you want to search. If the value is not a string, an error is returned.

  • pattern: required. A constant of the STRING type or a regular expression. This parameter specifies the pattern that must be matched by the specified substring. For more information about regular expressions, see Regular expressions. If pattern is an empty string or is of another data type, an error is returned.

  • start_position: optional. A constant of the BIGINT type. The value of this parameter must be greater than 0. If the value is of another data type or is less than or equal to 0, an error is returned. If you do not configure this parameter, the default value 1 is used. This value indicates that the search starts from the first character of the source string.

• Return value

  A value of the BIGINT type is returned. The return value varies based on the values of the input parameters.

  • If no substring is matched, 0 is returned.

  • If the value of source, pattern, or start_position is null, null is returned.

• Examples

  • Example 1: Calculate the number of substrings in abababc that match a specified pattern from the specified position. Sample statement:

    -- The return value is 1. 
    select regexp_count('abababc', 'a.c');
    -- The return value is 2. 
    select regexp_count('abababc', '[[:alpha:]]{2}', 3);

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select regexp_count('abababc', null);

  • Example 3: Calculate the number of colons (:) in the JSON string {"account_id":123456789,"account_name":"allen","location":"hangzhou","bill":100}. Sample statement:

    -- The return value is 4. 
    select regexp_count('{"account_id":123456789,"account_name":"allen","location":"hangzhou","bill":100}',':');

REGEXP_EXTRACT

• Syntax

  string regexp_extract(string <source>, string <pattern>[, bigint <groupid>])

• Description

  Splits the source string into groups based on a pattern and returns the string in the nth group specified by groupid.

• Parameters

  • source: required. A value of the STRING type. This parameter specifies the string that you want to split.

  • pattern: required. A constant of the STRING type or a regular expression. This parameter specifies the pattern that must be matched by the specified substring. For more information about regular expressions, see Regular expressions.

  • groupid: optional. A constant of the BIGINT type. The value of this parameter must be greater than or equal to 0.

  Note

  Data is stored in the UTF-8 format. Chinese characters can be represented in hexadecimal. They are encoded in the range of [\\x{4e00},\\x{9fa5}].

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If pattern is an empty string or no group is specified in pattern, an error is returned.

  • If the value of groupid is not of the BIGINT type or is less than 0, an error is returned. If you do not specify this parameter, the default value 1 is used. This value indicates that the string in the first group is returned. If the value of groupid is 0, all substrings that match pattern are returned.

  • If the value of source, pattern, or groupid is null, null is returned.

REGEXP_INSTR

• Syntax

  bigint regexp_instr(string <source>, string <pattern>[,bigint <start_position>[, bigint <occurrence>[, bigint <return_option>]]])

• Description

  Returns the start or end position of the substring that matches the value of pattern at the nth occurrence specified by occurrence, in the source string from the start position specified by start_position.

• Parameters

  • source: required. A value of the STRING type. This parameter specifies the source string.

  • pattern: required. A constant of the STRING type or a regular expression. This parameter specifies the pattern that must be matched by the specified substring. For more information about regular expressions, see Regular expressions. If pattern is an empty string, an error is returned.

  • start_position: optional. A constant of the BIGINT type. This parameter specifies the position from which the search starts. If you do not specify this parameter, the default value 1 is used.

  • occurrence: optional. A constant of the BIGINT type. If you do not configure this parameter, the default value 1 is used. This value indicates the position where a substring matches the pattern in the search for the first time.

  • return_option: optional. A constant of the BIGINT type. This parameter specifies that the start or end position of the substring that matches a specified pattern is returned. Valid values: 0 and 1. If this parameter is not configured, the default value 0 is used. If this parameter is set to an invalid number or a value of another data type, an error is returned. The value 0 indicates that the start position of the substring that matches a specified pattern is returned. The value 1 indicates that the end position of the substring that matches a specified pattern is returned.

• Return value

  A value of the BIGINT type is returned. This value indicates the start or end position of the matched substring in the source string based on the type specified by return_option. The return value varies based on the following rules:

  • If pattern is an empty string, an error is returned.

  • If the value of start_position or occurrence is not of the BIGINT type or is less than or equal to 0, an error is returned.

  • If the value of source, pattern, start_position, occurrence, or return_option is null, null is returned.

• Examples

  • Example 1: Return the start position of the substring that matches o[[:alpha:]]{1} at the second occurrence in the string i love www.taobao.com from the third character. Sample statement:

    -- The return value is 14. 
    select regexp_instr('i love www.taobao.com', 'o[[:alpha:]]{1}', 3, 2);

  • Example 2: Return the end position of the substring that matches o[[:alpha:]]{1} at the second occurrence in the string i love www.taobao.com from the third character. Sample statement:

    -- The return value is 16. 
    select regexp_instr('i love www.taobao.com', 'o[[:alpha:]]{1}', 3, 2, 1);

  • Example 3: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select regexp_instr('i love www.taobao.com', null, 3, 2);

REGEXP_REPLACE

• Syntax

  string regexp_replace(string <source>, string <pattern>, string <replace_string>[, bigint <occurrence>])

• Description

  Uses a string specified by replace_string to replace the substring that matches a pattern at the nth occurrence specified by occurrence in the source string and returns a result.

• Parameters

  • source: required. A value of the STRING type. This parameter specifies the strings that you want to replace.

  • pattern: required. A constant of the STRING type or a regular expression. This parameter specifies the pattern that must be matched by the specified substring. For more information about regular expressions, see Regular expressions. If pattern is an empty string, an error is returned.

  • replace_string: required. A value of the STRING type. The value is used to replace the string that matches the pattern.

  • occurrence: optional. A constant of the BIGINT type, which must be greater than or equal to 0. The value of this parameter indicates that the string that matches the specified pattern at the nth occurrence specified by occurrence is replaced with replace_string. If this parameter is set to 0, all substrings that match the specified pattern are replaced. If the value is of another data type or is less than 0, an error is returned. Default value: 0.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If the referenced group does not exist, no substring is replaced.

  • If the value of replace_string is null and a substring matches the pattern, null is returned.

  • If the value of replace_string is null but no substring matches the pattern, the original string is returned.

  • If the value of source, pattern, or occurrence is null, null is returned.

• Examples

  • Example 1: Use (\\1)\\2-\\3 to replace all substrings that match ([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4}) in the 123.456.7890 string. Sample statement:

    -- The return value is (123)456-7890. 
    select regexp_replace('123.456.7890', '([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4})',
    '(\\1)\\2-\\3', 0);

  • Example 2: Replace the substring that matches the specified pattern in the abcd string. Sample statement:

    -- The return value is a b c d. 
    select regexp_replace('abcd', '(.)', '\\1 ', 0);
    -- The return value is a bcd. 
    select regexp_replace('abcd', '(.)', '\\1 ', 1);
    -- The return value is abcd. 
    select regexp_replace('abcd', '(.)', '\\2', 1);

  • Example 3: The data of the url column in the table url_set is in the www.simple@xxx.com format and xxx in each row is different. Replace all content after www in the url column. Sample statement:

    -- The return value is wwwtest. 
    select regexp_replace(url,'(www)(.*)','wwwtest',0) from url_set;

  • Example 4: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select regexp_replace('abcd', '(.)', null, 0);

  • Example 5: The group that you want to reference does not exist. Sample statement:

    -- Only one group is defined in the pattern and the referenced group does not exist. 
    -- We recommend that you do not use this function in this manner. The result of referencing a nonexistent group is not defined. 
    regexp_replace("abcd", "(.*)(.)$", "\\2", 0) = "d"
    -- No group is defined in the pattern. Therefore, "\1" references a nonexistent group. 
    -- We recommend that you do not use this function in this manner. The result of referencing a nonexistent group is not defined. 
    regexp_replace("abcd", "a", "\\1", 0) = "bcd"

REGEXP_SUBSTR

• Syntax

  string regexp_substr(string <source>, string <pattern>[, bigint <start_position>[, bigint <occurrence>]])

• Description

  Returns a string that matches a pattern at the nth occurrence specified by occurrence, in the source string from the start position specified by start_position.

• Parameters

  • source: required. A value of the STRING type. This parameter specifies the string that contains the substring for which you want to search.

  • pattern: required. A constant of the STRING type or a regular expression. This parameter specifies the pattern that must be matched by the specified substring. For more information about regular expressions, see Regular expressions.

  • start_position: optional. A constant of the BIGINT type. The value of this parameter must be greater than 0. If you do not specify this parameter, the default value 1 is used. This value indicates that the search starts from the first character of the source string.

  • occurrence: optional. A constant of the BIGINT type. The value of this parameter must be greater than 0. If you do not specify this parameter, the default value 1 is used. This value indicates that the first matched substring is returned.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If pattern is an empty string, an error is returned.

  • If no substring matches the specified pattern, null is returned.

  • If the value of start_position or occurrence is not of the BIGINT type or is less than or equal to 0, an error is returned.

  • If the value of source, pattern, start_position, occurrence, or return_option is null, null is returned.

• Examples

  • Example 1: Return the substring in the I love aliyun very much string that matches a specified pattern. Sample statement:

    -- The return value is aliyun. 
    select regexp_substr('I love aliyun very much', 'a[[:alpha:]]{5}');
    -- The return value is have. 
    select regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1);
    -- The return value is 2. 
    select regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 2);

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select regexp_substr('I love aliyun very much', null);

REPEAT

• Syntax

  string repeat(string <str>, bigint <n>)

• Description

  Returns a string that contains str repeated n times.

• Parameters

  • str: required. A value of the STRING type. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation.

  • n: required. The value is of the BIGINT type. The returned string cannot exceed 2 MB in length.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If the value of str is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.

  • If n is left empty, an error is returned.

  • If the value of str or n is null, null is returned.

• Examples

  • Example 1: Return a string that repeats the string abc for 5 times. Sample statement:

    -- The return value is abcabcabcabcabc. 
    select repeat('abc', 5); 

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select repeat('abc', null);

REPLACE

• Syntax

  string replace(string <str>, string <old>, string <new>)

• Description

  If a part of the string specified by str exactly matches the string specified by old, this part of the string is replaced by the string specified by new, and the complete string after the replacement is returned. If no part of the string specified by str is exactly the same as the string specified by old, the original string is returned.

• Parameters

  • str: required. A value of the STRING type. This parameter specifies the string that you want to replace.

  • old: required. This parameter specifies the string that you want to use for comparison.

  • new: required. This parameter specifies the string that you want to use to replace the original string.

• Return values

  A value of the STRING type is returned. If an input parameter is set to null, null is returned.

• Examples

  • Example 1: Replace the part of the string ababab that is exactly the same as the string abab with the string 12. Sample statement:

    -- The return value is 12ab. 
    select replace('ababab','abab','12');

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select replace('123abab456ab',null,'abab');

REVERSE

• Syntax

  string reverse(string <str>)

• Description

  Returns a reversed copy of the string.

• Parameters

  str: required. A value of the STRING type. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If the value of str is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.

  • If the value of str is null, null is returned.

• Examples

  • Example 1: Return a string whose characters are in reverse order of the string I love aliyun very much. Sample statement:

    -- The return value is hcum yrev nuyila evol I. 
    select reverse('I love aliyun very much');

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select reverse(null);

RPAD

• Syntax

  string rpad(string <str1>, int <length>, string <str2>)

• Description

  Right pads str1 with str2 to a specified length.

• Parameters

  • str1: required. A value of the STRING type. This parameter specifies the string that you want to right pad.

  • length: required. A value of the INT type. This parameter specifies the number of characters that you want to use for right padding.

  • str2: required. This parameter specifies the string that you want to use to right pad another string.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If the value of length is less than the number of characters in str1, this function truncates str1 from the left to obtain a string with the number of characters specified by length.

  • If length is set to 0, an empty string is returned.

  • If no input parameters are available or an input parameter is set to null, null is returned.

• Examples

  • Example 1: Right pad the string abcdefgh with the string 12 to obtain a string with 10 characters in length. Sample statement:

    -- The return value is abcdefgh12. 
    select rpad('abcdefgh', 10, '12');

  • Example 2: Right pad the string abcdefgh with the string 12 to obtain a string with 5 characters in length. Sample statement:

    -- The return value is abcde. 
    select rpad('abcdefgh', 5, '12');

  • Example 3: The value of length is 0. Sample statement:

    -- The return value is an empty string. 
    select rpad('abcdefgh' ,0, '12'); 

  • Example 4: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select rpad(null ,0, '12');

RTRIM

• Syntax

  string rtrim(string <str>[, <trimChars>])
  string trim(trailing [<trimChars>] from <str>)

• Description

  Removes the characters from the right side of a string that is specified by str.

  • If you do not specify trimChars, the spaces on the right side are automatically removed.

  • If you specify trimChars, the substring that consists of one or more characters specified by trimChars is removed from the right side of the string that is specified by str.

• Parameters

  • str: required. A value of the STRING type. This parameter specifies the string from which the characters on the right side are removed. If the input value is of the BIGINT, DECIMAL, DOUBLE, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation.

  • trimChars: optional. A value of the STRING type. This parameter specifies the characters to be removed.

• Return value

  A value of the STRING type is returned. The return value varies based on the following rules:

  • If the value of str is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.

  • If the value of str or trimChars is null, null is returned.

• Examples

  • Example 1: Remove the space from the right side of the string yxTxyomxx . Sample statement:

    -- The return value is yxTxyomxx. 
    select rtrim(' yxTxyomxx ');
    -- The preceding statement is equivalent to the following statement: 
    select trim(trailing from ' yxTxyomxx ');

  • Example 2: Remove the substring that consists of one or more characters in the xy collection from the right side of the string yxTxyomxx.

    -- The return value is yxTxyom. If x or y appears on the right side of a string, it is removed. 
    select rtrim('yxTxyomxx', 'xy');
    -- The preceding statement is equivalent to the following statement: 
    select trim(trailing 'xy' from 'yxTxyomxx');

  • Example 3: The input parameter is set to null. Sample statement:

    -- The return value is null. 
    select rtrim(null);
    select ltrim('yxTxyomxx', 'null');

SPACE

• Syntax

  string space(bigint <n>)

• Description

  Generates a string of n spaces.

• Parameters

  n: required. The value is of the BIGINT type. The returned string cannot exceed 2 MB in length.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If n is left empty, an error is returned.

  • If the value of n is null, null is returned.

• Examples

  -- The return value is 10. 
  select length(space(10));

SPLIT_PART

• Syntax

  string split_part(string <str>, string <separator>, bigint <start>[, bigint <end>])

• Description

  Uses a delimiter specified by separator to split a string specified by str, and returns a substring that starts from the character specified by start and ends with the character specified by end.

• Parameters

  • str: required. A value of the STRING type. This parameter specifies the string that you want to split. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation.

  • separator: required. A constant of the STRING type. This parameter specifies the delimiter that is used to split the string. The delimiter can be a character or a string.

  • start: required. A constant of the BIGINT type. The value of this parameter must be greater than 0. This parameter specifies the start position of the substring to be returned. The position starts from 1.

  • end: a constant of the BIGINT type. The value of this parameter must be greater than or equal to start. This parameter specifies the end position of the substring to be returned. If this parameter is not specified, the value of this parameter is equal to start and the substring that starts from the character specified by start is returned.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If start is set to a value that is greater than the number of substrings, an empty string is returned. For example, if the string has 6 substrings but the start value is greater than 6, an empty string is returned.

  • If the value of separator does not exist in the string specified by str and start is set to 1, the string specified by str is returned. If str is an empty string, an empty string is returned.

  • If separator is an empty string, the original string specified by str is returned.

  • If the value of end is greater than the number of substrings, the substring starting from the character specified by start is returned.

  • If the value of str is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.

  • If the value of separator is not a constant of the STRING type, an error is returned.

  • If the value of start or end is not a constant of the BIGINT type, an error is returned.

  • If the value of an input parameter except separator is null, null is returned.

• Examples

  • Example 1: Use the comma (,) as a delimiter to split the string a,b,c,d and return the substring that matches the specified rule. Sample statement:

    -- The return value is a. 
    select split_part('a,b,c,d', ',', 1);
    -- The return value is a,b. 
    select split_part('a,b,c,d', ',', 1, 2);

  • Example 2: The value of start is greater than the number of substrings after the specified string is split into the substrings. Sample statement:

    -- The return value is an empty string. 
    select split_part('a,b,c,d', ',', 10);

  • Example 3: separator does not exist in the string specified by str. Sample statement:

    -- The return value is a,b,c,d. 
    select split_part('a,b,c,d', ':', 1);
    -- The return value is an empty string. 
    select split_part('a,b,c,d', ':', 2);

  • Example 4: separator is an empty string. Sample statement:

    -- The return value is a,b,c,d. 
    select split_part('a,b,c,d', '', 1);

  • Example 5: The value of end is greater than the number of substrings after the specified string is split into the substrings. Sample statement:

    -- The return value is b,c,d. 
    select split_part('a,b,c,d', ',', 2, 6);

  • Example 6: An input parameter except separator is set to null. Sample statement:

    -- The return value is null. 
    select split_part('a,b,c,d', ',', null);

SUBSTR

• Syntax

  string substr(string <str>, bigint <start_position>[, bigint <length>])

• Description

  Returns a substring that starts from start_position in a string specified by str and has a length specified by length.

• Parameters

  • str: required. A value of the STRING type. If the input value is of the BIGINT, DECIMAL, DOUBLE, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation.

  • start_position: required. A value of the BIGINT type. Default value: 1.

    • If start_position is set to 0, the return value is the same as the return value when this parameter is set to 1.

  • length: optional. A value of the BIGINT type. This parameter specifies the length of a substring. The value of this parameter must be greater than 0.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If the value of str is not of the STRING, BIGINT, DECIMAL, DOUBLE, or DATETIME type, an error is returned.

  • If the value of length is not of the BIGINT type or is less than or equal to 0, an error is returned.

  • If length is not configured, the substring from the start position to the end of str is returned.

  • If the value of str, start_position, or length is null, null is returned.

• Examples

  • Example 1: Return a substring that has the specified length and starts from the specified position in the string abc. Sample statement:

    -- The return value is bc. 
    select substr('abc', 2);
    -- The return value is b. 
    select substr('abc', 2, 1);
    -- The return value is bc. 
    select substr('abc',-2 , 2);

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select substr('abc', null);

SUBSTRING

• Syntax

  string substring(string|binary <str>, int <start_position>[, int <length>])

• Description

  Returns a substring that starts from start_position in a string specified by str and has a length specified by length.

• Parameters

  • str: required. A value of the STRING or BINARY type.

  • start_position: required. A value of the INT type. The start position starts from 1. If start_position is set to 0, an empty string is returned. If start_position is a negative value, the search starts from the end to the start of the string and the last character is -1.

  • length: optional. A value of the BIGINT type. This parameter specifies the length of a substring. The value of this parameter must be greater than 0.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If str is not of the STRING or BINARY type, an error is returned.

  • If the value of length is not of the BIGINT type or is less than or equal to 0, an error is returned.

  • If length is not specified, the substring from the start position to the end of str is returned.

  • If the value of str, start_position, or length is null, null is returned.

• Examples

  • Example 1: Return a substring that has the specified length that starts from the specified position in the string abc. Sample statement:

    -- The return value is bc. 
    select substring('abc', 2);
    -- The return value is b. 
    select substring('abc', 2, 1);
    -- The return value is bc. 
    select substring('abc',-2,2);
    -- The return value is ab. 
    select substring('abc',-3,2);
    -- The return value is 001. 
    substring(bin(2345), 2, 3);

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select substring('abc', null, null);

SUBSTRING_INDEX

• Syntax

  string substring_index(string <str>, string <separator>, int <count>)

• Description

  Truncates the string str to a substring from the first character to the nth delimiter. n is specified by count. If count is a positive value, the string is truncated from left to right. If count is a negative value, the string is truncated from right to left.

• Parameters

  • str: required. A value of the STRING type. This parameter specifies the string that you want to truncate.

  • separator: required. A delimiter of the STRING type.

  • count: required. The value is of the INT type. This parameter specifies the position of the delimiter.

• Return values

  A value of the STRING type is returned. If an input parameter is set to null, null is returned.

• Examples

  • Example 1: Truncate the string https://www.alibabacloud.com. Sample statement:

    -- The return value is https://www.alibabacloud. 
    select substring_index('https://www.alibabacloud.com', '.', 2);
    -- The return value is alibabacloud.com. 
    select substring_index('https://www.alibabacloud.com', '.', -2);

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select substring_index('https://www.alibabacloud.com', null, 2);

TO_CHAR

• Syntax

  string to_char(boolean <value>)
  string to_char(bigint <value>)
  string to_char(double <value>)
  string to_char(decimal <value>)

• Description

  Converts data of the BOOLEAN, BIGINT, DECIMAL, or DOUBLE type to the STRING type.

• Parameters

  value: required. A value of the BOOLEAN, BIGINT, DECIMAL, or DOUBLE type.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If value is not of the BOOLEAN, BIGINT, DECIMAL, or DOUBLE type, an error is returned.

  • If value is set to null, null is returned.

• Examples

  • Example 1: Convert values to the STRING type. Sample statement:

    -- The return value is 123. 
    select to_char(123);
    -- The return value is true. 
    select to_char(true);
    -- The return value is 1.23. 
    select to_char(1.23);

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select to_char(null);

TOLOWER

• Syntax

  string tolower(string <source>)

• Description

  Converts uppercase letters in a string specified by source to lowercase letters.

• Parameters

  source: required. A value of the STRING type. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation. Only English characters are supported.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If the value of source is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.

  • If the value of source is null, null is returned.

• Examples

  • Example 1: Convert uppercase letters in a string to lowercase letters. Sample statement:

    -- The return value is abcd. 
    select tolower('aBcd');
    -- The return value is china fighting. 
    select tolower ('China Fighting'); 

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select tolower(null);

TOUPPER

• Syntax

  string toupper(string <source>)

• Description

  Converts lowercase letters in a string specified by source to uppercase letters.

• Parameters

  source: required. A value of the STRING type. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation. Only English characters are supported.

• Return values

  A value of the STRING type is returned. The value null is returned in the following scenarios:

  • If the value of source is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.

  • If the value of source is null, null is returned.

• Examples

  • Example 1: Convert lowercase letters in a string to uppercase letters. Sample statement:

    -- The return value is ABCD. 
    select toupper('aBcd');
    -- The return value is CHINA FIGHTING. 
    select toupper ('China Fighting'); 

  • Example 2: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select toupper(null);

TRANSLATE

• Syntax

  string translate(string|varchar <str1>, string|varchar <str2>, string|varchar <str3>)

• Description

  In str1, replaces each character found in str2 with the corresponding character from str3. If characters in str1 do not exist in str2, no replacement occurs.

• Return values

  A value of the STRING type is returned. If an input parameter is set to null, null is returned.

• Examples

  • Example 1: Use the specified characters in the string cd to replace the characters that are included in ababab from abab. Sample statement:

    -- The return value is cdcdcd. 
    select translate('ababab','abab','cd');

  • Example 2: Use the specified characters in the string cdefg to replace the characters that are included in ababab from abab. Sample statement:

    -- The return value is cdcdcd. 
    select translate('ababab','abab','cdefg');

  • Example 3: An input parameter is set to null. Sample statement:

    -- The return value is null. 
    select translate('ababab','cd',null);

TRIM

• Syntax

  string trim(string <str>[,<trimChars>])
  string trim([BOTH] [<trimChars>] from <str>)

• Description

  Removes characters from the left and right sides of a string specified by str.

  • If you do not specify trimChars, the spaces on the left and right sides are automatically removed.

  • If you specify trimChars, the substrings that consist of one or more characters specified by trimChars are removed from the left and right sides of the string that is specified by str.

• Parameters

  • str: required. A value of the STRING type. This parameter specifies the string from which the characters on the left and right sides are removed. If the input value is of the BIGINT, DECIMAL, DOUBLE, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation.

  • trimChars: optional. A value of the STRING type. This parameter specifies the characters to be removed.

• Return value

  A value of the STRING type is returned. The return value varies based on the following rules:

  • If the value of str is not of the STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME type, an error is returned.

  • If the value of str or trimChars is null, null is returned.

• Examples

  • Example 1: Remove the spaces from the left and right sides of the string yxTxyomxx . Sample statement:

    -- The return value is yxTxyomxx. 
    select trim(' yxTxyomxx ');
    -- The preceding statement is equivalent to the following statement: 
    select trim(both from ' yxTxyomxx ');
    select trim(from ' yxTxyomxx ');

  • Example 2: Remove the substrings that consist of one or more characters in the xy collection from the left and right sides of the string yxTxyomxx.

    -- The return value is Txyom. If x or y appears on the left or right side, it is removed. 
    select trim('yxTxyomxx', 'xy');
    -- The preceding statement is equivalent to the following statement: 
    select trim(both 'xy' from 'yxTxyomxx');
    select trim('xy' from 'yxTxyomxx');

  • Example 3: The input parameter is set to null. Sample statement:

    -- The return value is null. 
    select trim(null);
    select trim('yxTxyomxx', null);