String Functions

CONCAT(expr1, expr2...)

Concatenates all listed strings.

SELECT str1, str2, str3, CONCAT(str1, str2, str3)

WHERE str1 IS NOT NUll

TEXTCAT(expr, expr)

Concatenates two strings.

SELECT str1, str2, TEXTCAT(str1, str2)

WHERE str1 IS NOT NUll

STRING_FORMAT(pattern[, args...])

Formats strings based on the Java string format.

SELECT str1, STRING_FORMAT(str1, '%s')

WHERE str1 IS NOT NUll

LENGTH(expr)

Returns the length of a string, which is the number of UTF-16 characters in the string.

SELECT LENGTH(str1)

WHERE str1 IS NOT NUll

LOWER(expr)

Converts a string into lowercase letters.

SELECT LOWER(str1)

WHERE str1 IS NOT NUll

POSITION(string1 IN string2 [FROM fromIndex])

Returns the index of the position where string1 appears in string2 for the first time. The search starts from the specified index. If no index is specified, the search starts from index1. If string1 does not exist in string2, 0 is returned.

SELECT POSITION(str1 IN str2 FROM 5)

REGEXP_EXTRACT(expr, pattern, [index])

Extracts a string that matches a specified regular expression in expr. The index starts from 1. If no match is found, null is returned. If no index is specified or the index is 0, the first matched substring is returned. For exact match, add a caret (^) before the regular expression and a dollar sign ($) after the regular expression.

SELECT REGEXP_EXTRACT(str1, '[A-Za-z]+://[A-Za-z0-9.-]+(/[^ ]*)', 5)

REGEXP_LIKE(expr, pattern)

Checks whether a string matches a specified regular expression. For exact match, add a caret (^) before the regular expression and a dollar sign ($) after the regular expression. The usage of this function is similar to that of the LIKE statement. The difference is that the LIKE statement searches for content with a specified pattern.

SELECT REGEXP_LIKE(str1, '\.(jpg|jpeg|png|gif)$')

REPLACE(expr, pattern, replacement)

Uses replacement to replace the substring same as pattern in expr.

SELECT REPLACE(expr,pattern, replacement)

STRPOS(string1, string2)

Returns the index of the position where string2 appears in string1 for the first time. The search starts from index1. If no result is found, 0 is returned.

SELECT STRPOS(str1, str2)

WHERE str1 IS NOT NUll

AND str2 IS NOT NUll

SUBSTRING(expr, index, [length])

Subtracts a string. length indicates how many UTF-16 characters will be extracted.

SELECT SUBSTRING(str1, 3, 10)

WHERE str1 IS NOT NUll

RIGHT(expr, [length])

Truncates a specified length from the right to the left of a string.

SELECT RIGHT(str1, 5)

WHERE str1 IS NOT NUll

LEFT(expr, [length])

Truncates a specified length from the left to the right of a string.

SELECT LEFT(str1, 5)

WHERE str1 IS NOT NUll

SUBSTR(expr, index, [length])

Same as SUBSTRING.

SELECT SUBSTR(str1, 3, 10)

WHERE str1 IS NOT NUll

UPPER(expr)

Converts a string into uppercase letters.

SELECT UPPER(str1)

WHERE str1 IS NOT NUll

REVERSE(expr)

Reverses the character string.

SELECT REVERSE(str1)

WHERE str1 IS NOT NUll

LPAD(expr, length, chars)

Adds specified characters to the left of a string until the string reaches the specified length. If the specified length is less than the actual string length, the string is truncated based on the specified length. If the string or the specified character is null, null is returned. If the specified character is empty, no padding is performed. Characters may be deleted if necessary.

SELECT LPAD(str1, 50, 'testStr')

WHERE str1 IS NOT NUll

RPAD(expr, length, chars)

Adds specified characters to the right of a string until the string reaches the specified length. If the specified length is less than the actual string length, the string is truncated based on the specified length. If the string or the specified character is null, null is returned. If the specified character is empty, no padding is performed. Characters may be deleted if necessary.

SELECT RPAD(str1, 50, 'testStr')

WHERE str1 IS NOT NUll

CONTAINS_STRING(<expr>, str)

Checks whether expr contains the str string.

SELECT CONTAINS_STRING(log_level,'warn')

ICONTAINS_STRING(<expr>, str)

Checks whether expr contains the str string. The string is not case sensitive.

SELECT ICONTAINS_STRING(log_level,'WARN')