tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Stream Compute Service
    Documentation
    Download PDF

    String Functions

    Last updated: 2023-11-08 14:17:35
    Download PDF

    Functions

    Function
    Description
    string1 || string2
    Returns the concatenation of string1 and string2. This is equivalent to CONCAT(string1, string2).
    CHAR_LENGTH(string)
    Returns the number of characters in string.
    CHARACTER_LENGTH(string)
    Same as CHAR_LENGTH(string).
    UPPER(string)
    Returns string in uppercase.
    LOWER(string)
    Returns string in lowercase.
    POSITION(string1 IN string2)
    Returns the position (starts from 1) of the first occurrence of string1 in string2; returns 0 if string1 cannot be found in string2.
    TRIM({BOTH |LEADING |TRAILING }string1 FROM string2 )
    Returns a string that removes leading and/or trailing characters string1 from string2. By default, spaces at both sides are removed.
    LTRIM(string)
    Returns a string that removes the left spaces from string. For example, LTRIM(' Hello') returns 'Hello'.
    RTRIM(string)
    Returns a string that removes the right spaces from string. For example, RTRIM(' World ') returns ' World'.
    REPEAT(string, integer)
    Returns a string that repeats the base string integer times. For example, REPEAT('Meow', 3) returns 'MeowMeowMeow'.
    REGEXP_REPLACE(string1, string2, string3)
    Returns a string from string1 with all the substrings that match a regular expression string2 being replaced with string3. For example, REGEXP_REPLACE('banana', 'a|n', 'A') returns 'bAAAAA'.
    REPLACE(string1, string2, string3)
    Returns a string from string1 with all the substrings that match string2 being replaced with string3. For example, REPLACE('banana', 'a', 'A') returns 'bAnAnA'.
    OVERLAY(string1 PLACING string2 FROM start_pos [ FOR length ])
    Returns a string that replaces length characters of string1 with string2 from position start_pos (starts from 1).
    SUBSTRING(string from pos [ FOR length])
    Returns a substring of string starting from position pos with length (to the end by default). pos starts from 1 instead of 0.
    REGEXP_EXTRACT(string1, string2[, integer])
    Returns a string from string1 which extracted with a specified regular expression string2 and a regex match group index integer. You can specify the group index (starts from 1) using the third parameter integer. If you do not specify the group index or specify it as 0, the string that matches the whole regex is returned. For example, REGEXP_EXTRACT('foothebar', 'foo(.*?)(bar)', 2) returns 'bar'.
    INITCAP(string)
    Returns a new form of string with the first character of each word converted to uppercase and the remaining characters to lowercase. For example, INITCAP('i have a dream') returns 'I Have A Dream'.
    CONCAT(string1, string2 …)
    Returns a string that concatenates multiple strings (string1, string2, …). Returns NULL if any string is NULL.
    CONCAT_WS(separator, string1, string2, …)
    Returns a string that concatenates multiple strings (string1, string2, …) with separator. Returns NULL if separator is NULL. Automatically skips NULL strings, but not empty strings. For example, CONCAT_WS('~', 'AA','BB', '', 'CC') returns AA~BB~~CC.
    LPAD(text, length, padding)
    Returns a new string from text left-padded with padding to length characters. If the length of text is longer than length, returns text shortened to length characters.
    RPAD(text, length, padding)
    Returns a new string from text right-padded with padding to length characters. If the length of text is longer than length, returns text shortened to length characters.
    FROM_BASE64(string)
    Returns the Base64-decoded result from string. Returns NULL if string is NULL.
    TO_BASE64(string)
    Returns the Base64-encoded result from string.
    ASCII(string)
    Returns the ASCII code of the first character in string. Returns NULL if string is NULL. For example, ASCII('an apple') returns 97 (the first character a corresponds to 97).
    CHR(integer)
    Returns the ASCII character corresponding to integer. For example, CHR(97) returns a.
    ENCODE(string, charset)
    Encodes string into a BINARY using the provided character set charset. Example: ENCODE(hello, 'GBK')
    DECODE(binary, charset)
    Decodes binary into a string using the provided character set charset. Example: DECODE(binary_field, 'UTF-16LE')
    INSTR(string1, string2)
    Returns the position of the first occurrence of string2 in string1. Returns NULL if any parameter is NULL.
    LEFT(string, n)
    Returns the leftmost n characters from string. Returns an empty string if n is negative. Returns NULL if any parameter is NULL.
    RIGHT(string, n)
    Returns the rightmost n characters from string. Returns an empty string if n is negative. Returns NULL if any parameter is NULL.
    LOCATE(string1, string2[, integer])
    Returns the position of the first occurrence of string1 in string2 after position integer (parameters are in reverse order compared with the INSTR function). Returns 0 if string1 is not found. Returns NULL if any parameter is NULL.
    REGEXP(string, regex)
    Returns TRUE if any substring of string matches the regular expression regex, otherwise FALSE. Returns NULL if any parameter is NULL.
    REVERSE(string)
    Returns the reversed string. Returns NULL if any parameter is NULL.
    SPLIT_INDEX(string, separator, index)
    Splits string by separator, and returns the indexth string (VARCHAR) of the split strings. index starts from 0.
    SPLIT(string, separator)
    Splits string by separator, and returns a Row object.
    STR_TO_MAP(string1[, string2, string3])
    Returns MAP<string, string> after splitting string1 into key-value pairs using separators. string2 is the pair separator (default: ,), and string3 is the key-value separator (default: =). For example, STR_TO_MAP('k1=v1,k2=v2,k3=v3') returns key-value pairs {'k1': 'v1', 'k2': 'v2', 'k3': 'v3'}.
    SUBSTR(string[, pos[, length]])
    Returns a substring of string starting from position pos with length (to the end by default).
    EXPLODE(inputStr, separator)
    Splits a string into a temporary table with multiple rows. This function is a table function, and you need to use the keyword LATERAL TABLE ( ) to reference this dynamically generated temporary table as the right table for JOIN.
    GET_ROW_ARITY(row)
    Gets the number of columns for Row object row.
    GET_ROW_FIELD_STR(row, index)
    Gets the value of the indexth column in Row object row. index starts from 0. The return value is of VARCHAR type.
    GET_JSON_OBJECT(json_str, path_str)
    Gets the elements of a JSON string json_str at the JSON path specified by path_str, which can be arbitrarily nested. Supported JSONPath syntax: $ for root object, . for a child element, [] for an array index, and * as a wildcard for array index [].
    IS_ALPHA(content)
    Checks whether a string contains only letters.
    IS_DIGITS(content)
    Checks whether a string contains only digits.
    MD5(string)
    Returns the MD5 hash of string.
    
    
    SHA1
    Returns the SHA-1 hash of expr.
    SHA256
    Returns the SHA-256 hash of expr.

    Examples

    ||

    Feature description: Returns the concatenation of string1 and string2. This is equivalent to CONCAT(string1, string2). Syntax: string1 || string2 Example statement: SELECT string1 || string2 FROM Test; Test data and result:
    Test Data (VARCHAR string1)
    Test Data (VARCHAR string2)
    Test Result (VARCHAR)
    Oce
    anus
    Oceanus

    CHAR_LENGTH

    Feature description: Returns the number of characters in string. Syntax: CHAR_LENGTH(string) Example statement: SELECT CHAR_LENGTH(var1) AS length FROM Test; Test data and result:
    Test Data (VARCHAR var1)
    Test Result (INT length)
    Oceanus
    7

    CHARACTER_LENGTH

    Feature description: Same as CHAR_LENGTH(string). Syntax: CHARACTER_LENGTH(string) Example statement: SELECT CHAR_LENGTH(var1) AS length FROM Test; Test data and result:
    Test Data (VARCHAR var1)
    Test Result (INT length)
    Oceanus
    7

    LOWER

    Feature description: Returns string in lowercase. Syntax: LOWER(string) Example statement: SELECT LOWER(var1) AS lower FROM Test; Test data and result:
    Test Data (VARCHAR var1)
    Test Result (VARCHAR lower)
    OCeanus
    oceanus

    UPPER

    Feature description: Returns string in uppercase. Syntax: UPPER( string) Example statement: SELECT UPPER(var1) AS upper FROM Test; Test data and result:
    Test Data (VARCHAR var1)
    Test Result (VARCHAR upper)
    OCeanus
    OCEANUS

    TRIM

    Feature description: Returns a string that removes leading and/or trailing characters string1 from string2. By default, spaces at both sides are removed. Syntax: TRIM({BOTH | LEADING | TRAILING } string1 FROM string2 ) Example statement: SELECT TRIM(BOTH string1 FROM string2) AS res FROM Test; Test data and result:
    Test Data (VARCHAR string1)
    Test Data (VARCHAR string2)
    Test Result (VARCHAR res)
    a
    aoceanusa
    oceanus

    CONCAT

    Feature description: Concatenates two or more strings. Automatically skips NULL parameters. Syntax: CONCAT( string1, string2 …) Example statement: SELECT CONCAT('123', '456', 'abc', 'def') AS res FROM Test; Test data and result: '123456abcdef'
    Test Data (VARCHAR string1)
    Test Data (VARCHAR string2)
    Test Data (VARCHAR string3)
    Test Data (VARCHAR string4)
    Test Result (VARCHAR res)
    123
    456
    abc
    def
    123456abcdef

    CONCAT_WS

    Feature description: Returns a string that concatenates multiple strings (string1, string2, …) with separator. Returns NULL if separator is NULL. Automatically skips NULL strings, but not empty strings. Syntax: CONCAT_WS(separator, string1, string2, …) Example statement: SELECT CONCAT_WS(separator, string1,string2, string3) AS res FROM Test; Test data and result:
    Test Data (VARCHAR separator)
    Test Data (VARCHAR string1)
    Test Data (VARCHAR string2)
    Test Data (VARCHAR string3)
    Test Result (VARCHAR res)
    -
    AA
    BB
    CC
    AA-BB-CC

    INITCAP

    Feature description: Returns a new form of string with the first character of each word converted to uppercase and the remaining characters to lowercase. Syntax: INITCAP(string) Example statement: SELECT INITCAP(var1) AS str FROM Test; Test data and result:
    Test Data (VARCHAR var1)
    Test Result (VARCHAR str)
    i have a dream
    I Have A Dream

    IS_ALPHA

    Feature description: Checks whether a string contains only letters. Syntax: IS_ALPHA(content) Example statement: SELECT IS_ALPHA(content) AS result FROM Test; Test data and result:
    Test Data (VARCHAR content)
    Test Result (BOOLEAN result)
    Oceanus
    true
    oceanus123
    false
    ''
    false
    null
    false

    IS_DIGITS

    Feature description: Checks whether a string contains only digits. Syntax: IS_DIGITS(content) Example statement: SELECT IS_DIGITS(content) AS result FROM Test; Test data and result:
    Test Data (VARCHAR content)
    Test Result (BOOLEAN case_result)
    58.0
    true
    58
    true
    58pl
    false
    ''
    false
    null
    false

    LPAD

    Feature description: Returns a new string from text left-padded with padding to length characters. If the length of text is longer than length, returns text shortened to length characters. Syntax: LPAD(text , length , padding) Example statement: SELECT LPAD(text, length, padding) AS res FROM Test; Test data and result:
    Test Data (VARCHAR text)
    Test Data (INT length)
    Test Data (VARCHAR padding)
    Test Result (VARCHAR res)
    oceanus
    3
    hello
    hel
    oceanus
    -1
    hello
    ''
    oceanus
    12
    hello
    hellooceanus

    RPAD

    Feature description: Returns a new string from text right-padded with padding to length characters. If the length of text is longer than length, returns text shortened to length characters. Syntax: RPAD(text , length , padding) Example statement: SELECT RPAD(text, length, padding) AS res FROM Test; Test data and result:
    Test Data (VARCHAR text)
    Test Data (INT length)
    Test Data (VARCHAR padding)
    Test Result (VARCHAR res)
    oceanus
    3
    hello
    oce
    oceanus
    -1
    hello
    ''
    oceanus
    12
    hello
    oceanushello

    MD5

    Feature description: Returns the MD5 hash of string. Syntax: MD5(string) Example statement: SELECT MD5(content) AS res FROM Test; Test data and result:
    Test Data (VARCHAR content)
    Test Result (VARCHAR res)
    abc
    900150983cd24fb0d6963f7d28e17f72

    OVERLAY

    Feature description: Returns a string that replaces length characters of string1 with string2 from position start_pos (starts from 1). Syntax: SELECT OVERLAY(string1 PLACING string2 FROM start_pos [ FOR length ]) Example statement: SELECT OVERLAY(string1 PLACING string2 FROM start_pos FOR length) AS res FROM Test; Test data and result:
    Test Data (VARCHAR string1)
    Test Data (VARCHAR string2)
    Test Data (INT start_pos)
    Test Data (INT length)
    Test Result (VARCHAR res)
    oceanus
    abc
    2
    2
    oabcanus

    POSITION

    Feature description: Returns the position of the first occurrence of string1 in string2; returns 0 if string1 cannot be found in string2. Syntax: POSITION(string1 IN string2) Example statement: SELECT POSITION(string1 IN string2) AS res FROM Test; Test data and result:
    Test Data (VARCHAR string1)
    Test Data (VARCHAR string2)
    Test Result (VARCHAR res)
    nu
    oceanus
    5

    GET_JSON_OBJECT(json_str, path_str)

    Feature description: Gets the elements of a JSON string json_str at the JSON path specified by path_str, which can be arbitrarily nested. Supported JSONPath syntax: $ for root object, . for a child element, [] for an array index, and * as a wildcard for array index []. Syntax: GET_JSON_OBJECT(json_str, path_str) Example statement: SELECT GET_JSON_OBJECT(json_str, path_str) AS res FROM Test; Test data and result:
    Test Data (VARCHAR json_str)
    {"school": {"student":[{"num":8,"type":"A"},{"num":9,"type":"B"}],"teacher":{"num":200,"type":"A"} },"headmaster":"mark" }
    Test Data (VARCHAR path_str)
    Test Result (VARCHAR res)
    $.school
    {\\"student\\":[{\\"num\\":8,\\"type\\":\\"A\\"},{\\"num\\":9,\\"type\\":\\"B\\"}],
    \\"teacher\\":{\\"num\\":200,\\"type\\":\\"A\\"}}
    $.school.student[1]
    {\\"num\\":9,\\"type\\":\\"B\\"}
    $.school.teacher
    {\\"num\\":200,\\"type\\":\\"A\\"}
    $.headmaster
    mark

    REPLACE

    Feature description: Returns a string from string1 with all the substrings that match string2 being replaced with string3. Syntax: REPLACE(string1, string2, string3) Example statement: SELECT REPLACE( string1, string2, string3) AS res FROM Test; Test data and result:
    Test Data (VARCHAR string1)
    Test Data (VARCHAR string2)
    Test Data (VARCHAR string3)
    Test Result (VARCHAR res)
    banana
    a
    A
    bAnAnA

    SHA1

    Feature description: Returns the SHA-1 hash of expr. Syntax: SHA1(expr) Example statement: SELECT SHA1(expr) AS res FROM Test; Test data and result:
    Test Data (VARCHAR expr)
    Test Result (VARCHAR res)
    abc
    a9993e364706816aba3e25717850c26c9cd0d89d

    SHA256

    Feature description: Returns the SHA-256 hash of expr. Syntax: SHA256(expr) Example statement: SELECT SHA256(expr) FROM Test; Test data and result:
    Test Data (VARCHAR expr)
    Test Result (VARCHAR res)
    abc
    ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad
    Contact Us

    Contact our sales team or business advisors to help your business.

    Contact Us
    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    Submit a Ticket
    7x24 Phone Support
    Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
    Privacy PolicyLegalCookie Policy