Shulou(Shulou.com)05/31 Report--

This article mainly introduces the string functions commonly used in MySQL, which have a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to know about it.

A database function is a functional module 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.

CONCAT ()

CONCAT (str1,str2, …)) Function is used to return a string after multiple string concatenations, such as:

SELECT CONCAT ('MySQL',' string', 'function') AS str

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

SELECT CONCAT ('MySQL', NULL,' function') AS str

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

SELECT 'MySQL'' string 'function' AS str

The above method can only be used for concatenating string constants, not for values of concatenating fields.

If SQL mode PIPES_AS_CONCAT,MySQL logic or operator (| |) is enabled, it can also be used to concatenate strings, similar to Oracle and PostgreSQL.

Except for CONCAT (str1,str2, …)) Outside of the function, CONCAT_WS (separator,str1,str2,...) Function indicates that multiple strings are concatenated using the specified delimiter separator, and NULL is returned if the delimiter is NULL. For example:

SELECT CONCAT_WS ('-', 'MySQL', NULL,' string') AS str1

LOWER ()

The 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

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

SELECT LOWER (BINARY 'MySQL string function') AS str1

UPPER ()

The 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

LENGTH ()

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

SELECT LENGTH ('MySQL string function') AS len1, OCTET_LENGTH ('MySQL string function') AS len2

In utf8mb4 coding, a 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, that is, the number of characters. For example:

SELECT CHAR_LENGTH ('MySQL string function') AS len1, CHARACTER_LENGTH ('MySQL string function') AS len2

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

SELECT BIT_LENGTH ('MySQL string function') AS len

A byte contains 8 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 the substring starting from the specified position pos. Len indicates the length of the returned substring; a pos of 0 indicates an empty string. For example:

SELECT SUBSTRING ('MySQL string function', 6) AS str1

The position parameter pos can be negative, and the returned substring starts with the pos character on the right side of the string. For example:

SELECT SUBSTRING ('MySQL string function',-2) AS str1

In addition, the SUBSTR () and MID () functions are synonymous with the SUBSTRING () function and support the above four forms.

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

SELECT LEFT ('MySQL string function', 5) AS str1

The 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 ('Zhang San; Li Si; Wang Wu','; 2) AS str1, [object Object]

TRIM ()

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

SELECT TRIM ('MySQL string function') AS str1

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

SELECT TRIM (LEADING''FROM' MySQL string function') AS str1

LPAD () / RPAD ()

The LPAD (str,len,padstr) function represents that the left side of the string str is populated with padstr until the len;RPAD (str,len,padstr) function indicates that the right side of the string str is filled with padstr 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.

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

For example:

INSTR ()

The INSTR (str,substr) function returns the index position of the substring substr in the string str for the first time, and returns 0 if the substring is not found. For example:

Select INSTR ('MySQL string function', 'string') AS index1

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

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

SELECT LOCATE ('sparkling MySQL Server', 5) AS ind

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

SELECT FIELD ('Li Si', 'Zhang San','Li Si', 'Wang Wu') AS ind

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

SELECT FIND_IN_SET ('Li Si', 'Zhang San, Li Si, Wang Wu') AS ind

REPLACE ()

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

SELECT REPLACE ('MySQL string function', 'string', 'date') AS str1

In addition, the 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

REVERSE ()

The 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"; Thank you for reading this article carefully. I hope the article "what are the string functions commonly used in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us, pay attention to the industry information channel, and more related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.