MySQL implements string concatenation, interception, replacement, and position search operations

MySQL string concatenation, interception, replacement, and search position.

Commonly used string functions:

1. String concatenation

1.1 CONCAT(s1,s2,...) function

Returns the string generated by the connection parameters, one or more contents to be concatenated. If any one of them is NULL, the return value is NULL.

SELECT CONCAT('Current time:',NOW()); -- Output result: Current time: 2019-01-17 11:27:58

1.2 CONCAT_WS(x,s1,s2,...) function

Returns a string consisting of multiple strings concatenated together, with an x ​​between each string.

SELECT CONCAT_WS(';','pan_junbiao's blog','KevinPan','pan_junbiao'); -- Output: pan_junbiao's blog;KevinPan;pan_junbiao

2. String interception

2.1 SUBSTRING(s,n,len) and MID(s,n,len) functions

The two functions have the same effect, returning a string starting from the nth character and with a length of len from the string s.

SELECT SUBSTRING('Hello, welcome to pan_junbiao's blog',8,14); -- Output result: pan_junbiao's blogSELECT MID('Hello, welcome to pan_junbiao's blog',8,14); -- Output result: pan_junbiao's blog

2.2 LEFT(s,n), RIGHT(s,n) functions

The former returns the n characters starting from the leftmost of the string s, and the latter returns the n characters starting from the rightmost of the string s.

SELECT LEFT('Hello, welcome to pan_junbiao's blog',7); -- Output result: Hello, welcome to SELECT RIGHT('Hello, welcome to pan_junbiao's blog',14); -- Output result: pan_junbiao's blog

3. String replacement

3.1 INSERT(s1,x,len,s2) function

Returns the string s1 with the substring starting at position x replaced by len characters of string s2.

SELECT INSERT('Hello, welcome to A Biao's blog',8,2,'pan_junbiao'); -- Output: Hello, welcome to pan_junbiao's blog

3.2 REPLACE(s,s1,s2) function

Returns a string that replaces all occurrences of string s1 in string s with string s2.

SELECT REPLACE('Hello, welcome to A Biao's blog', 'A Biao', 'pan_junbiao'); -- Output: Hello, welcome to pan_junbiao's blog

4. String query position

4.1 LOCATE(str1,str), POSITION(str1 IN str), INSTR(str,str1) functions

The three functions have the same function, returning the starting position of the substring str1 in the string str (starting from the nth character).

SELECT LOCATE('pan_junbiao','Hello, welcome to pan_junbiao's blog'); -- Output: 8
SELECT POSITION('pan_junbiao' IN 'Hello, welcome to pan_junbiao's blog'); -- Output: 8
SELECT INSTR('Hello, welcome to pan_junbiao's blog','pan_junbiao'); -- Output: 8

4.2 FIELD(s,s1,s2,...) function

Returns the position of the first match of string s.

SELECT FIELD('pan_junbiao','pan_junbiao's blog','KevinPan','阿标','pan_junbiao'); -- Output: 4

Supplement: mysql find string position instr() and LOCATE() string search functions

INSTR(str,substr)

Returns the position of the first occurrence of a substring within the string str. This is identical to the two-argument form of LOCATE(), except that the order of the arguments is reversed.

mysql> SELECT INSTR('foobarbar', 'bar');
    -> 4
mysql> SELECT INSTR('xbar', 'foobar');
    -> 0

LOCATE(substr,str) , LOCATE(substr,str,pos)

The first syntax returns the first occurrence of substring substr within string str. The second syntax returns the first occurrence of substring substr in string str, starting at position pos. If substr is not in str, the return value is 0.

mysql> SELECT LOCATE('bar', 'foobarbar');
    -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
    -> 0
mysql> SELECT LOCATE('bar', 'foobarbar',5);
    -> 7

This function supports multibyte characters and is case sensitive only if at least one of its arguments is a binary string.

POSITION(substr IN str)

Returns the position of the first occurrence of substring substr in string str. If the substring substr does not exist in str, the return value is 0:

mysql> SELECT POSITION('bar', 'foobarbar');
-> 4
mysql> SELECT POSITION('xbar', 'foobar');
-> 0

Efficiency test

SELECT * FROM `o_soft` WHERE LOCATE('d200',tid2)>0

MySQL returns an empty result set (zero rows). (Query took 0.0050 seconds)

SELECT * FROM `o_soft` WHERE INSTR('d200',tid2)>0

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.