This topic describes the string functions that are supported by and those which are not supported by PolarDB-X.

Supported functions

PolarDB-X supports the following string functions.

Function Description Example
ASCII(s) Returns the ASCII code of the first character in the s string. Execute the following statement to return the ASCII code of the first character in the "CustomerName" string.

SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;
CHAR_LENGTH(s) Returns the number of characters in the s string. Execute the following statement to return the number of characters in the "RUNOOB" string.

SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
CHARACTER_LENGTH(s) Returns the number of characters in the s string. Execute the following statement to return the number of characters in the "RUNOOB" string.

SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
CONCAT(s1,s2...sn) Concatenates the s1 string, the s2 string, and other strings. Execute the following statement to concatenate multiple strings.

SELECT CONCAT("SQL ", "Runoob ", "Google ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2...sn) This function is a special form of CONCAT(s1,s2,...).This function concatenates the s1 string, the s2 string, and other strings. The x argument is the separator for the rest of the arguments. Execute the following statement to concatenate multiple strings and separate the strings with the first argument:

SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") AS ConcatenatedString;
FIELD(s,s1,s2...) Returns the position of the s string in a list of strings that include the s1 string and the s2 string. Execute the following statement to return the position of the "c" string in the list of strings. The string list is composed of the "a" string, the "b" string, the "c" string, the "d" string, and the "e" string.

SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2) Returns the position of the s1 substring within the s2 string. Execute the following statement to return the position of the "c" substring within the string that is composed of the "a", "b", "c", "d", and "e" substrings.

SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n) Returns the number x in the '#,###.##' format.##", The function rounds the number x to n decimal places. Execute the following statement to return 250500.5634 in the '#,###.##' format.

SELECT FORMAT(250500.5634, 2);

The following result is returned:

-- 250,500.56
INSERT(s1,x,len,s2) Replaces a substring of the s1 string with the s2 string. x specifies the position of the first character to replace in the s1 string. len specifies the length of the replaced substring. Execute the following statement to replace the first six characters of the "example.com" string with the "runoob" string.

SELECT INSERT("example.com", 1, 6, "runoob");

The following result is returned:

-- runoobe.com
LOCATE(s1,s) Returns the position of the first occurrence of the s1 substring in the s string.
  • Execute the following statement to return the position of the first occurrence of the "st" substring in the "myteststring" string.

    SELECT LOCATE('st','myteststring');

    The following result is returned:

    -- 5

  • Execute the following statement to return the position of the first occurrence of the "b" substring in the "abc" string.

    SELECT LOCATE('b', 'abc');

    The following result is returned:

    -- 2
LCASE(s) Returns the s string in lowercase. Execute the following statement to return the "RUNOOB" string in lowercase.

SELECT LCASE('RUNOOB');

The following result is returned:

-- runoob
LEFT(s,n) Returns the first n characters of the s string. Execute the following statement to return the first two characters of the "runoob" string.

SELECT LEFT('runoob',2);

The following result is returned:

-- ru
LOWER(s) Returns the s string in lowercase. Execute the following statement to return the "RUNOOB" string in lowercase.

SELECT LOWER('RUNOOB');

-- runoob
LPAD(s1,len,s2) Left-pads the s1 string with the s2 string to a length of len characters. Execute the following statement to left-pad the "abc" string with the "xx" string.

SELECT LPAD('abc',5,'xx')

The following result is returned:

-- xxabc
LTRIM(s) Removes the leading space of the s string. Execute the following statement to remove the leading space of the "RUNOOB" string.

SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;

The following result is returned:

-- RUNOOB
MID(s,n,len) Extracts a substring from the s string. n specifies the position of the first occurrence of the substring in the s string. len specifies the length of the substring. This function is a synonym for SUBSTRING(s,n,len). Execute the following statement to extract a substring from the "RUNOOB" string. The substring starts at the second character of the "RUNOOB" string and contains three characters.

SELECT MID("RUNOOB", 2, 3) AS ExtractString;

The following result is returned:

-- UNO
POSITION(s1 IN s) Returns the position of the first occurrence of the s1 substring in the s string. Execute the following statement to return the position of the first occurrence of the "b" substring in the "abc" string.

SELECT POSITION('b' in 'abc');

The following result is returned:

-- 2
REPEAT(s,n) Repeats the s string n times. Execute the following statement to repeat the "runoob" string three times.

SELECT REPEAT('runoob',3);

The following result is returned:

-- runoobrunoobrunoob
REPLACE(s,s1,s2) Replaces the s1 substring of the s string with the s2 string. Execute the following statement to replace the "a" character in the "abc" string with the "x" character.

SELECT REPLACE('abc','a','x');

The following result is returned:

--xbc
REVERSE(s) Reverses the characters in the s string. Execute the following statement to reverse the characters in the "abc" string.

SELECT REVERSE('abc');

The following result is returned:

-- cba
RIGHT(s,n) Returns the last n characters of the s string. Execute the following statement to return the last two characters of the "runoob" string.

SELECT RIGHT('runoob',2);

The following result is returned:

-- ob
RPAD(s1,len,s2) Right-pads the s1 string with the s2 string to a length of len characters. Execute the following statement to right-pad the "abc" string with the "xx" string.

SELECT RPAD('abc',5,'xx');

The following result is returned:

-- abcxx
RTRIM(s) Removes the trailing spaces of the s string. Execute the following statement to remove the trailing spaces of the "RUNOOB" string.

SELECT RTRIM("RUNOOB") AS RightTrimmedString;

The following result is returned:

-- RUNOOB
SPACE(n) Returns a string that consists of n spaces. Execute the following statement to return a string that consists of 10 spaces.

SELECT SPACE(10);
STRCMP(s1,s2) Compares the s1 string and the s2 string based on ASCII values. If the ASCII values of the strings are the same, the value 0 is returned. If the ASCII value of the s1 string is larger than that of the s2 string, the value 1 is returned. If the ASCII value of the s1 string is smaller than that of the s2 string, the value -1 is returned. Execute the following statement to compare the "runoob" string and the "runoob" string based on ASCII values.

SELECT STRCMP("runoob", "runoob");

The following result is returned:

-- 0
SUBSTR(s, start, length) Extracts a substring from the s string. start specifies the position of the first occurrence of the substring in the s string. length specifies the length of the substring. Execute the following statement to extract a substring from the "RUNOOB" string. The substring starts at the second character of the "RUNOOB" string and contains three characters.

SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString;

The following result is returned:

-- UNO
SUBSTRING(s, start, length) Extracts a substring from the s string. start specifies the position of the first occurrence of the substring in the s string. length specifies the length of the substring. Execute the following statement to extract a substring from the "RUNOOB" string. The substring starts at the second character of the "RUNOOB" string and contains three characters.

SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString;

The following result is returned:

-- UNO
SUBSTRING_INDEX(s, delimiter, number) Returns a substring of the s string. The delimiter argument specifies the delimiter in the string.

If number is positive, the function locates the 'number'th delimiter by counting from the left and returns all characters to the left of this delimiter.

If number is negative, the function locates the 'number'th delimiter by counting from the right and returns all characters to the right of this delimiter. 'number' is the absolute value of the number argument.

  • SELECT SUBSTRING_INDEX('a*b','*',1);

    The following result is returned:

    -- a

  • SUBSTRING_INDEX('a*b','*',-1);

    The following result is returned:

    -- b

  • SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1);

    The following result is returned:

    -- c
TRIM(s) Removes the leading spaces and trailing spaces from the s string.

Removes the leading spaces and trailing spaces from the "RUNOOB" string.

SELECT TRIM('RUNOOB') AS TrimmedString;
UCASE(s) Converts a string to uppercase letters.

Execute the following statement to convert the "runoob" string to uppercase letters.

SELECT UCASE("runoob");

The following result is returned:

-- RUNOOB
UPPER(s) Converts a string to uppercase letters.

Execute the following statement to convert the "runoob" string to uppercase letters.

SELECT UPPER("runoob");

The following result is returned:

-- RUNOOB

Functions that are not supported

Compared with MySQL5.7, PolarDB-X does not support the following string functions:

Function Description
LOAD_FILE() Loads a file.
MATCH Performs a full-text search.
SOUNDS LIKE Compares strings that sound the same.