Common functions of MySQL (full)

I. Using String functions

1. Combining the String Functions CONCAT () and CONCAT_WS () 1.1 Functions CONCAT () the CONCAT () function joins the passed arguments to return the merged string type data. If one of the parameters is null, the return value is null.

SELECT CONCAT ('My','S','QL') after merging strings;

SELECT CONCAT (Curdate (),12.34) after merging strings;

SELECT CONCAT ('My','S','QL',  NULL) after the merged string;

1.2 Function Concat_ws ()

Concat_ws (Sep,s1,s2,... SN) function, compared to concat (), has an sep parameter that represents the delimiter, that is, not only joins the other parameters that are passed in, but also splits the individual strings through the delimiter. The delimiter can be a string, or it can be a different parameter. If the delimiter is null, the returned result is null. The function ignores null values after any delimiter parameters.

SELECT Concat_ws ('-','029',88461234) merged string

Execute the SQL function Concat_ws (), when the value of the delimiter parameter is NULL, the specific SQL statement is as follows:

SELECT Concat_ws (NULL,'029',88461234) merged string;

The result of the execution shows that when the first parameter value passed in is null, the returned result value will be null, so the returned result is null.

Execute the SQL function Concat_ws (), when the value of the delimiter parameter has NULL, the specific SQL statement is as follows:

SELECT Concat_ws ('-','029',NULL,88461234) Merged string;

The result of the execution shows that when the parameter passed in (except for the first argument) has NULL, the returned result value ignores null, so the returned result is 029-88461234.

2. Compare string size function strcmp ()

The STRCMP (STR1,STR2) function is used to compare string parameters str1 and str2, if the parameter str1 is greater than str2, the result 1 is returned, and if the parameter str1 is less than str2, the result is 1, and if the parameter str1 equals str2, the result 0 is returned.

SELECT STRCMP ('abc','abd'),    STRCMP ('  ABC','abc'),    STRCMP ('ABC  ','ABB');

3. Get the string length function, Length (), and number of characters function char_length ()

Gets the length of the string through the function length () and Char_length (). The length (str) function obtains the parameter of the descendant str, and the char_length (str) function Gets the string of the parameter str of the descendant.

3.1 Function Length ()

SELECT ' MySQL ' English strings, Length ('MySQL') string character lengths,' Changjiangong ' Chinese string, length (' Changjiangong ') string character length

Since an English character takes up one byte, the string 'MySQL' has a length of 5, because a kanji character accounts for 3 characters (some books say 2), so the string ' Changjiangong ' The length is 6.

3.2 Function Char_length ()

SELECT ' MySQL ' English string, char_length ('MySQL') string character length,' Changjiangong ' Chinese string, Char_length (' Changjiangong ') string character length

The string 'MySQL' has a total of 5 characters, but occupies 6 bytes of space. This is because each string ends with a, and the Terminator is also occupied by a byte space.

4. Implement the letter case conversion function upper () and the number of characters function lower () 4.1 functions Upper () and the UCase () function converts all letters in a string to uppercase letters.

SELECT ' MySQL ' string,UPPER('mysql') converted string, UCASE ('mysql' ) after the converted string;

The 4.2 function lower () and the LCase () function convert all letters in a string to lowercase letters.

SELECT ' MYSQL ' string,LOWER('mysql') converted string, LCASE ('mysql' ) after the converted string;

5. Finding strings

5.1 function Find_in_set () that returns the position of the string.

The position of the matched string is obtained through the function Find_in_set (STR1,STR2), which returns the position of the string in the string str2 that matches the str1, and the parameter str2 string contains several strings separated by commas.

SELECT Find_in_set ('MySQL','orcle,sql server,mysql') position;

5.2 Function to return the specified string position field ()

Gets the position of the matching string by using the function field (STR,STR1,STR2 ...). The function returns the position of the first string that matches the string str.

SELECT FIELD ('MySQL','orcle','SQL Server' ,'MySQL') location;

5.3 Return substring matches the start position

You can get the starting position of a substring match by three functions, which are functions locate (), POSITION (), and InStr ().

The LOCATE (STR1,STR) function returns the starting position of the string str1 in the parameter str. The other two functions are defined as follows: POSITION (str1 in str) INSTR (STR,STR1).

SELECT LOCATE ('SQL','MySQL') location, POSITION ('  SQL'in'MySQL') location, INSTR (' MySQL ','SQL') location;

5.4 Function Elt that returns the string at the specified position ()

Through function Elt (N,STR1,STR2 ...) Gets the string at the specified position, and the function returns the nth string.

SELECT ELT (1,'MySQL','orcle','SQL Server '  ') A string of position 1th;

5.5 Selecting a String function Make_set ()

A string can be obtained from the function Make_set (NUM,STR1,STR2...STRN), which first converts the value num to a binary number, and then selects the corresponding string from the parameter str1,str2,......,strn as a binary number. When a string is selected by a binary number, the value is read from right to left, and if the value is a value of 1, the string is selected, otherwise the string is not selected.

SELECTBIN (5) binary number, Make_set (5,'MySQL','Oracle','SQL Server','PostgreSQL') After the selected string, BIN (7) binary number, Make_set (7,'MySQL','Oracle','SQL Server','PostgreSQL') after the selected string;

6. Intercepting substrings from an existing string

In MySQL, a rich function is provided to implement the Intercept substring function, left (), function right (), function substring (), and function mid ().

6.1 Truncate substring from left or right

Left (Str,num): Returns a string in the string str that contains the first num letters (number of digits from the Ieft).

Right (Str,num): Returns a string in string str that contains the number of digits from the left.

SELECT ' MySQL ' string,left ('MySQL',2) first two characters, right(' MySQL ',3) after three characters;

6.2 Intercept specified position and length substrings

SUBSTRING (Str,num,len): Returns a substring in the string str with the beginning of the length len of the first position.

MID (Str,num,len)

SELECT ' Oraclemysql ' string,SUBSTRING('oraclemysql',7,5) intercepts the substring, MID ( ' Oraclemysql ',7,5) intercept substring;

7. Remove the leading and trailing spaces of a string

In MySQL, a rich function is provided to implement the function of removing the string whitespace, namely function ltrim (), function RTrim () and function trim (). LTRIM (str) Removes the space at the beginning of the string, RTRIM (str) strips the space at the end of the string, and TRIM (str) strips the string from the leading and trailing spaces.

SELECTCONCAT ('-','MySQL','-') The original string, Char_length (CONCAT ('-','MySQL','-') ) The original string length, CONCAT ('-',LTRIM('MySQL'),'-') after processing the string, Char_length (CONCAT ('-',LTRIM('MySQL'),'-')) string length after processing

8. Replace string 8.1 with the Insert () function Insert (STR,POS,LEN,NEWSTR): This function replaces the string ewstr in string str with the start of the POS position at the beginning of Len. If the value of the parameter pos exceeds the string length, the return value is the original string str. If Len is longer than the length of the string remaining in the original string (str), all substitutions begin at position pos. If either parameter is NULL, the return value is null.

SELECT ' This is the MySQL database management system ' string,     INSERT (' This is MySQL database management system ',3,5,'Oracle' ) after the converted string;

Execute the SQL function insert (), when the replacement start position is greater than the string length, the specific SQL statement is as follows:

SELECT ' This is the MySQL database management system ' string, char_length (' This is the MySQL database management system ') string character number    ,INSERT( ' This is the MySQL database management system ',+,+,'Oracle') converted string;

Execute the SQL function insert (), and when the length of the replacement is greater than the length of the string remaining in the original string, replace all from the starting position with the following SQL statement:

SELECT ' This is the MySQL database management system ' string, char_length ('MySQL database management system ') number of characters left    ,INSERT('  This is the MySQL database management system ',3,'Oracle' ) after the converted string;

8.2 Using the Replace () function

Replace (STR,SUBSTR,NEWSTR)): This function replaces the substring substr in the string str with the string newstr.

SELECT ' This is the MySQL database management system ' the original string    , REPLACE (' This is MySQL database management system ','mysql','Oracle  ') after the replacement string;

Two. Using numeric functions

I. Using Date functions

I. Using System Information functions

Common functions of MySQL (full)