Commonly used basic operation syntax of mysql (11)~~String function [command line mode]

Note: SQL is relatively portable, but functions are not. They are generally unique to database software. For example, mysql has mysql functions, and oracle has oracle functions.

1. Concat connection string:

As can be seen from the above figure, you can connect any two or more by directly using select concat The string can also be used to connect query results. In general, it is also used to connect query results. It should be noted that if the content of the connection contains null, the result will only be null.

2. concat_ws uses delimiters to connect strings:

#In the above syntax, the first character represents the specified connection format. , the others represent the string content that needs to be connected. Another difference from concat is that if null appears here, null will be ignored.

3. strcmp compares the size of strings:

As you can see in the picture above, when the first string is smaller than the second one Returns -1, otherwise returns 1, if equal, returns 0.

4, length and char_length to get the length of the string:

It can be seen from the figure that length calculates the length in bytes, while char_length calculates the length in characters.

5. Use upper or ucase and lower or lcase to convert English characters to uppercase or lowercase:

6. find_in_set search string The position:

As you can see from the figure, the string to be found must meet a certain format, that is, it needs to be separated by commas, so it cannot be understood as a subtitle here. String.

7. Use field to find the position of the string:

Note the difference between this and the previous one, one is in the same string Separated by commas, and here are several strings.

8, locate, position and instr find the position of the string:

The functions of the three here are basically the same, but the syntax used is slightly different There are differences. Position should be used in combination with in. The parameter positions of locate and instr are opposite. They both return the position of the substring in a string.

9. Use elt to return the string at the specified position. The syntax format is similar to field:

10. Intercept from the existing string Substring:

where left refers to intercepting the string of specified length starting from the leftmost point, right means starting from the right; the first numeric parameter of substring and mid refers to the starting position of interception, The second numeric parameter refers to the length of the interception.

11. Remove spaces from a string:

ltrim removes the spaces on the left, rtrim removes the spaces on the right, and trim removes the spaces before and after. It's just not easy to see here.

12. Insert string replacement:

As can be seen from the figure, the first parameter is the original string, and the second parameter is The actual position that needs to be replaced, the third one represents the length that needs to be replaced, and the fourth one represents the new string used to replace the original string; it should be noted that when the specified length that needs to be replaced exceeds the remaining length, all will be replaced. When the specified position is one greater than the length of the string, the new string will be added to the end. When the specified position is greater than the length of the string by more than two, the original string will remain unchanged.

13. replace string:

Different from the above, here is not to specify the position and length, but to directly specify a character. String, if this string does not exist in the original string, the replacement fails.

The above is the content of mysql common basic operation syntax (11)~~string function [command line mode]. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!