Detailed explanation of the top ten commonly used string functions in MySQL

Hello everyone! I am Mr. Tony who only talks about techniques and doesn’t cut hair.

A database function is a module that has some functionality, can receive zero or more input values, and return an output value. MySQL provides us with many system functions for processing and analyzing data. This article introduces 10 commonly used string functions and other related functions.

CONCAT()

CONCAT(str1,str2,…)) function is used to return a string after multiple strings are concatenated, for example:

SELECT CONCAT('MySQL', 'string', 'function') AS str;
str |
--------------+
MySQL string functions |

If any parameter in this function is NULL, the return result is NULL. For example:

SELECT CONCAT('MySQL', NULL, 'function') AS str;
str|
---+
   |

For string constants, we can also directly concatenate them together. For example:

SELECT 'MySQL' 'string' 'function' AS str;
str |
--------------+
MySQL string functions |

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

📝If the SQL mode PIPES_AS_CONCAT is enabled, the MySQL logical OR operator (||) can also be used to concatenate strings, similar to Oracle and PostgreSQL.

In addition to the CONCAT(str1,str2,…)) function, CONCAT_WS(separator,str1,str2,…)) function concatenates multiple strings using the specified separator separator. If the separator is NULL, NULL is returned. For example:

SELECT CONCAT_WS('-', 'MySQL', NULL, 'string') AS str1,
       CONCAT_WS(NULL, 'MySQL', 'string') AS str2;
str1 |str2|
-----------+----+
MySQL-String | |

LOWER()

LOWER(str) and LCASE(str) functions are used to convert a string to lowercase, for example:

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

MySQL case conversion functions do not support binary strings (BINARY, VARBINARY, BLOB) and can be converted to non-binary strings before processing. For example:

SELECT LOWER(BINARY 'MySQL string function') AS str1,
       LOWER(CONVERT(BINARY 'MySQL string function' USING utf8mb4)) AS str2;
str1 |str2 |
-------------------+-------------+
MySQL string function |

UPPER()

UPPER(str) and UCASE(str) functions are used to convert a string to uppercase, for example:

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

LENGTH()

LENGTH(str) and OCTET_LENGTH(str) functions are used to return the byte length of a string, for example:

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

In utf8mb4 encoding, a Chinese character occupies 3 bytes.

In addition, CHAR_LENGTH(str) and CHARACTER_LENGTH(str) functions are used to return the character length of a string, that is, the number of characters. For example:

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

The BIT_LENGTH(str) function is used to return the bit length (number of bits) of a string, for example:

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

A byte contains 8 bits.

SUBSTRING()

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 a substring starting from the specified position pos. len indicates the length of the returned substring; if pos is 0, an empty string is returned. For example:

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

The position parameter pos can be a negative number, in which case the returned substring starts from the posth character on the right side of the string. For example:

SELECT LEFT('MySQL string function',5) AS str1,
       RIGHT('MySQL string function',5) AS str2;
str1 |str2 |
-----+---------+
MySQL|String Functions|

In addition, SUBSTR() and MID() functions are synonyms of SUBSTRING() function and also support the above 4 forms.

LEFT(str,len) function returns len characters from the left side of the string str, and RIGHT(str,len) function returns len characters from the right side of the string str. For example:

SELECT LEFT('MySQL string function',5) AS str1,
       RIGHT('MySQL string function',5) AS str2;
str1 |str2 |
-----+---------+
MySQL|String Functions|

SUBSTRING_INDEX(str,delim,count) function returns the substring before the count-th delimiter delim. If count is a positive number, count starts from the left and returns all characters on the left; if count is a negative number, count starts from the right and returns all characters on the right. For example:

SELECT SUBSTRING_INDEX('张三;李四;王五', ';', 2) AS str1,
       SUBSTRING_INDEX('张三;李四;王五', ';', -2) AS str2;
str1 |str2 |
--------+--------+
Zhang San; Li Si|Li Si; Wang Wu|

TRIM()

TRIM([remstr FROM] str) function is used to return the substring after deleting all remstr strings on both sides of the string str. The default value of remstr is space. For example:

SELECT TRIM('MySQL string function') AS str1,
       TRIM('-' FROM '--MySQL string function--') AS str2;
str1 |str2 |
--------------+--------------+
MySQL string functions |MySQL string functions |

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) function is used to return the substring after deleting all remstr strings on both sides/left side/right side of the string str. By default, both sides (BOTH) are deleted, and remstr defaults to space. For example:

SELECT TRIM(LEADING ' ' FROM 'MySQL string function') AS str1,
       TRIM(TRAILING '-' FROM '--MySQL string function--') AS str2;
str1 |str2 |
----------------+----------------+
MySQL string functions |--MySQL string functions |

LPAD()/RPAD()

LPAD(str,len,padstr) function indicates that the left side of the string str is padded with padstr until the length is len; RPAD(str,len,padstr) function indicates that the right side of the string str is padded with padstr until the length is len. For example:

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 |

When the length of the string str is greater than len, it is equivalent to truncating 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. For example:

SELECT REPEAT('🔥', 5) AS str;
str |
----------+
🔥🔥🔥🔥🔥|

INSTR()

INSTR(str,substr) function is used to return the index position of the first occurrence of the substring substr in the string str, and returns 0 if the substring is not found. For example:

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

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 order of the parameters is reversed.

LOCATE(substr,str,pos) function returns the index position of the first occurrence of the substring substr in the string str starting from position pos, for example:

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

The FIELD(str,str1,str2,str3,…) function returns the position where the string str appears in the subsequent string list, and returns 0 if it is not found. For example:

SELECT FIELD('Li Si', 'Zhang San', 'Li Si', 'Wang Wu') AS ind;
ind|
---+
  2|

The FIND_IN_SET(str,strlist) function returns the position where the string str appears in the list string strlist. strlist consists of N substrings separated by commas. For example:

SELECT FIND_IN_SET('Li Si', 'Zhang San, Li Si, Wang Wu') AS ind;
ind|
---+
  2|

REPLACE()

The REPLACE(str,from_str,to_str) function is used to replace all from_str in the string str with to_str and return the replaced string. For example:

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

In addition, INSERT(str,pos,len,newstr) function is used to insert the substring newstr after the specified position pos of the string str, replacing the subsequent len ​​characters. For example:

SELECT INSERT('MySQL string function', 6, 3, 'date') AS str;
str |
------------+
MySQL Date Functions |

REVERSE()

REVERSE(str) function is used to reverse the order of characters in the string str. For example:

SELECT REVERSE('Shanghai tap water comes from the sea')='Shanghai tap water comes from the sea' AS "Palindrome";
Palindrome|
----+
   1|

If you want to learn more about MySQL, you can refer to this article.

This concludes this article on the top ten commonly used string functions in MySQL. For more relevant MySQL string function content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!