A database function is a functional module that accepts zero or more input values and returns an output value. MySQL provides many system functions for processing and analyzing data. This article introduces 10 commonly used string functions, as well as other related functions.

CONCAT()

CONCAT (str1 str2,…). The) function returns multiple concatenated strings, for example:

SELECT CONCAT('MySQL', 'string ',' function ') AS STR; STR | -- -- -- -- -- -- -- -- -- -- -- -- -- - | + MySQL string functionsCopy the code

If any of the arguments in this function are NULL, the result is NULL. Such as:

SELECT CONCAT('MySQL', NULL, 'function ') AS STR; str| ---+ |Copy the code

We can also concatenate string constants directly. Such as:

SELECT 'MySQL' AS STR; STR | -- -- -- -- -- -- -- -- -- -- -- -- -- - | + MySQL string functionsCopy the code

The above method can only be used to concatenate string constants, not field values.

PIPES_AS_CONCAT πŸ“ if you enable SQL mode, MySQL the Boolean or operator (| |) can also be used for the connection string, similar to the Oracle and PostgreSQL.

In addition to the CONCAT (str1 str2,…). Separator,str1,str2…) The separator function concatenates multiple strings using the specified separator, returning NULL if the separator is NULL. Such as:

SELECT CONCAT_WS('-', 'MySQL', NULL, 'string ') AS str1, CONCAT_WS(NULL, 'MySQL',' string ') AS str2; Str1 | str2 | -- -- -- -- -- -- -- -- -- -- - + - + MySQL - string | |Copy the code

LOWER()

The LOWER(STR) and LCASE(STR) functions are used to convert strings to lowercase, for example:

SELECT LOWER('MySQL string function ') AS str1, LCASE('MySQL string function ') AS str2; Str1 | str2 | -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + mysql | | mysql string string function functionCopy the code

MySQL case conversion function does not support BINARY strings (BINARY, VARBINARY, BLOB). It can be converted to non-binary strings and processed later. Such as:

SELECT LOWER(BINARY 'MySQL string function ') AS str1, LOWER(CONVERT(BINARY 'MySQL string function 'USING utf8mb4)) AS str2; Str1 | str2 | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + MySQLa c such Β¦ a ΒΈ squared a 1/2 level Γ¦ Β° | | mysql string functionsCopy the code

UPPER()

The UPPER(STR) and UCASE(STR) functions are used to convert strings to uppercase, for example:

SELECT UPPER('MySQL string function ') AS str1, UCASE('MySQL string function ') AS str2; Str1 | str2 | -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + MYSQL | | MYSQL string string function functionCopy the code

LENGTH()

The LENGTH(STR) and OCTET_LENGTH(STR) functions are used to return the LENGTH of a string in bytes, for example:

SELECT LENGTH('MySQL string function ') AS len1, OCTET_LENGTH('MySQL string function ') AS len2; len1|len2| ----+----+ 20| 20|Copy the code

In UTF8MB4 encoding, a Kanji character occupies three bytes.

In addition, the CHAR_LENGTH(STR) and CHARACTER_LENGTH(STR) functions are used to return the character length of the string, that is, the number of characters. Such as:

SELECT CHAR_LENGTH('MySQL string function ') AS len1, CHARACTER_LENGTH('MySQL string function ') AS len2; len1|len2| ----+----+ 10| 10|Copy the code

The BIT_LENGTH(STR) function returns the length (number of bits) of the string, for example:

SELECT BIT_LENGTH('MySQL string function ') AS len; len| ---+ 160|Copy the code

A byte contains eight bits.

SUBSTRING()

The SUBSTRING(STR,pos), SUBSTRING(STR FROM pos), SUBSTRING(STR,pos,len), and SUBSTRING(STR FROM pos FOR len) functions can all be used to return pos FROM a specified position The initial substring, len, returns the length of the substring; Pos 0 means an empty string is returned. Such as:

SELECT SUBSTRING('MySQL string function ', 6) AS str1, SUBSTRING('MySQL string function 'FROM 6) AS str2, SUBSTRING('MySQL string function ', 6) AS str2, SUBSTRING('MySQL string function ', 6) 3) AS str3, SUBSTRING('MySQL string function 'FROM 6 FOR 3) AS str4, SUBSTRING('MySQL string function ', 0) AS str5; Str1 | str2 | str3 | str4 | str5 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + - + string functions | | | string string string functions | |Copy the code

The positional argument pos can be negative, in which case the returned substring begins with the first pos character to the right of the string. Such as:

SELECT SUBSTRING('MySQL string function ', -2) AS str1, SUBSTRING('MySQL string function ', -5, 3) AS str2; Str1 | str2 | -- -- -- -- -- - + -- -- -- -- -- - | | + function stringsCopy the code

In addition, the SUBSTR() and MID() functions are synonyms for the SUBSTRING() function and support all four forms.

LEFT(STR,len) returns len characters to the LEFT of the string STR, and RIGHT(STR,len) returns len characters to the RIGHT of the string STR. Such as:

SELECT LEFT('MySQL string function ',5) AS str1, RIGHT('MySQL string function ',5) AS str2; Str1 | str2 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + MySQL | | string functionsCopy the code

The SUBSTRING_INDEX(STR,delim,count) function returns the substring before the count separator delim. If count is positive, count from the left and return all characters from the left; If count is negative, count from the right and return all characters on the right. Such as:

SELECT SUBSTRING_INDEX(' SUBSTRING_INDEX '; Li si. Cathy ', '; ', 2) AS str1, SUBSTRING_INDEX(' c '; Li si. Cathy ', '; ', -2) AS str2; Str1 | str2 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + zhang SAN. Li si | li si; Fifty |Copy the code

TRIM()

TRIM([remstr FROM] STR) returns the TRIM([remstr FROM] STR) function that removes all remstr substrings on either side of STR. Remstr defaults to Spaces. Such as:

SELECT TRIM(' MySQL string function ') AS str1, TRIM('-' FROM '--MySQL string function --') AS str2; Str1 | str2 | -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + MySQL | | MySQL string string function functionCopy the code

TRIM ([{BOTH | LEADING | TRAILING} [remstr] FROM] STR) function is used to delete the string STR/left/right on BOTH sides after all remstr string substring, the default delete on BOTH sides of the string (BOTH), Remstr defaults to Spaces. Such as:

SELECT TRIM(TRAILING '-' FROM 'MySQL string function ') AS str1, TRIM(TRAILING '-' FROM '--MySQL string function --') AS str2; Str1 | str2 | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + MySQL | | - MySQL string string function functionCopy the code

LPAD()/RPAD()

The LPAD(STR,len,padstr) function means that the left side of the string STR is padded with padstr until the length is len; The RPAD(STR,len,padstr) function means padding the right side of the string STR with padstr until the length is len. Such as:

SELECT LPAD(123, 6, '0') AS str1, LPAD(123, 2, '0') AS str2,
       RPAD(123, 6, '0') AS str1, RPAD(123, 2, '0') AS str1;
str1  |str2|str1  |str1|
------+----+------+----+
000123|12  |123000|12  |
Copy the code

When the length of string STR is greater than len, it truncates the string from the right.

In addition, the REPEAT(STR,count) function is used to copy the string STR count times and return the result. Such as:

SELECT REPEAT('πŸ”₯', 5) AS STR; STR | -- -- -- -- -- -- -- -- -- -- + πŸ”₯ πŸ”₯ πŸ”₯ πŸ”₯ πŸ”₯ |Copy the code

INSTR()

The INSTR(STR,substr) function returns the index position of the first occurrence of the substring substr in the string STR, or 0 if no substring is found. Such as:

Select INSTR('MySQL string function ', 'date ') AS index1, INSTR('MySQL string function ',' date ') AS index2, INSTR('MySQL string function ', 'date ') AS index3, INSTR('MySQL string function ', null) AS index4; index1|index2|index3|index4| ------+------+------+------+ 6| 0| 1| |Copy the code

In addition, the LOCATE(substr, STR) function can also be used to return the index position of the first occurrence of the substring substr in the string STR, the only difference from the INSTR(STR,substr) function is that the arguments are in reverse order.

The LOCATE(substr, STR,pos) function returns the index position of the substring substr that first appears in the string STR starting at position pos, for example:

SELECT LOCATE('S','MySQL Server', 5) AS ind;
ind|
---+
  7|
Copy the code

FIELD (STR, str1, str2 str3,…). The STR () function returns the position of the string STR in a subsequent string list, or 0 if it is not found. Such as:

SELECT FIELD(1, 1, 2) AS ind; ind| ---+ 2|Copy the code

The FIND_IN_SET(STR,strlist) function returns the position of the string STR in the list string strlist, which consists of N substrings separated by commas. Such as:

SELECT FIND_IN_SET AS ind; SELECT FIND_IN_SET AS ind; ind| ---+ 2|Copy the code

REPLACE()

The REPLACE(STR,from_str,to_str) function replaces all from_str in the string STR with to_str, returning the replaced string. Such as:

SELECT REPLACE('MySQL string function ', 'string ',' date ') AS str1, REPLACE('MySQL string function ', 'string ', '') AS str2; Str1 | str2 | -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | | MySQL MySQL date function functionCopy the code

In addition, the INSERT(STR,pos,len,newstr) function is used to INSERT the substring newstr after the specified position pos of string STR, replacing len of the following characters. Such as:

SELECT INSERT('MySQL string function ', 6, 3, 'date ') AS STR; STR | -- -- -- -- -- -- -- -- -- -- -- - | + MySQL date functionCopy the code

REVERSE()

The REVERSE(STR) function is used to REVERSE the order of characters in the string STR. Such as:

SELECT REVERSE(' Shanghai tap water from sea ')=' Shanghai tap water from sea '; Palindrome | -- - | + 1Copy the code