String Functions
Description
SQL provides the string function for concatenating strings and converting between uppercase and lowercase letters. For details, see Table 1.
![](https://support.huaweicloud.com/intl/en-us/usermanual-lts/public_sys-resources/note_3.0-en-us.png)
In the SQL syntax, characters must be enclosed in single quotation marks ('). Fields or table names are not enclosed or are enclosed with double quotation marks ("). For example, 'msg' indicates the character string msg, and msg or "msg" indicates the structured msg field.
Syntax
SELECT (fieldname1 || fieldname2) AS fieldname1_fieldname2
String Function Statements
Statement |
Description |
Example |
---|---|---|
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') |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot