Summarize the commonly used string functions in MySQL!

A database function is a module with a certain function that 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, as well as other related functions.

CONCAT()

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

SELECT CONCAT('MySQL', '字符串', '函数') AS str;

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

SELECT CONCAT('MySQL', NULL, '函数') AS str;

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

SELECT 'MySQL' '字符串' '函数' AS str;

The above method can only be used to connect string constants and cannot be used to connect field values.

If 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, the CONCAT_WS(separator,str1,str2,…)) function means to use the specified separator separator to connect multiple strings, and the separator is NULL. Return NULL. For example:

SELECT CONCAT_WS('-', 'MySQL', NULL, '字符串') AS str1,

LOWER()

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

SELECT LOWER('MySQL字符串函数') AS str1, LCASE('MySQL字符串函数') AS str2;

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

SELECT LOWER(BINARY 'MySQL字符串函数') AS str1,

UPPER()

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

SELECT UPPER('MySQL字符串函数') AS str1, UCASE('MySQL字符串函数') AS str2;

LENGTH()

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

SELECT LENGTH('MySQL字符串函数') AS len1, OCTET_LENGTH('MySQL字符串函数') AS len2;

In utf8mb4 encoding, one Chinese character occupies 3 bytes.

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

SELECT CHAR_LENGTH('MySQL字符串函数') AS len1, CHARACTER_LENGTH('MySQL字符串函数') AS len2;

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

SELECT BIT_LENGTH('MySQL字符串函数') AS len;

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

SELECT SUBSTRING('MySQL字符串函数', 6) AS str1,

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

SELECT SUBSTRING('MySQL字符串函数', -2) AS str1,

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

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

SELECT LEFT('MySQL字符串函数',5) AS str1,

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

SELECT SUBSTRING_INDEX('张三;李四;王五', ';', 2) AS str1,[object Object]

TRIM()

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

SELECT TRIM('  MySQL字符串函数  ') AS str1,

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

SELECT TRIM(LEADING ' ' FROM '  MySQL字符串函数  ') AS str1,

LPAD()/RPAD()

The LPAD(str,len,padstr) function means that the left side of the string str is filled with padstr until the length is len; RPAD( str,len,padstr) function means to use padstr on the right side of the string str until the length is len. For example:

SELECT LPAD(123, 6, '0') AS str1, LPAD(123, 2, '0') AS str2,

When the length of the string str is greater than len, it is equivalent to truncating the string from the right side.

In addition, the REPEAT(str,count) function is used to copy the string str count times and return the result.

For example:

INSTR()

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

select INSTR('MySQL字符串函数', '字符串') AS index1,

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, which is the only difference from the INSTR (str,substr) function. That is, the order of parameters is reversed.

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

SELECT LOCATE('S','MySQL Server', 5) AS ind;

FIELD (str,str1 ,str2,str3,…) The 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('李四', '张三', '李四', '王五') AS ind;

FIND_IN_SET (str,strlist) The 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('李四', '张三,李四,王五') AS ind;

REPLACE()

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字符串函数', '字符串', '日期') AS str1,

另外，INSERT (str,pos,len,newstr) 函数用于在字符串 str 的指定位置 pos 之后插入子串 newstr，替换随后的 len 个字符。例如：

SELECT INSERT('MySQL字符串函数', 6, 3, '日期') AS str;

REVERSE()

REVERSE (str) 函数用于将字符串 str 中的字符顺序进行反转。例如：

SELECT REVERSE('上海自来水来自海上')='上海自来水来自海上' AS "回文";

推荐学习：《mysql视频教程》

The above is the detailed content of Summarize the commonly used string functions in MySQL!. For more information, please follow other related articles on the PHP Chinese website!