Detailed explanation of MySQL string splicing, intercepting, replacing, and searching location examples

MySQL string splicing, intercepting, replacing, and searching location.

Commonly used string functions:

function	illustrate
CONCAT(s1,s2，...)	Returns the string generated by the connection parameter, one or more contents to be spliced, and any one is NULL, the return value is NULL.
CONCAT_WS(x,s1,s2,...)	Returns the string after multiple strings are spliced, with an x between each string.
SUBSTRING(s,n,len)、MID(s,n,len)	The two functions have the same function, and return a string starting from the nth character and length len from the string s.
LEFT(s,n)、RIGHT(s,n)	The former returns n characters from the leftmost point of string s, and the latter returns n characters from the rightmost point of string s.
INSERT(s1,x,len,s2)	Returns the string s1, whose substring starts at position x, and is replaced by the string s2.
REPLACE(s,s1,s2)	Returns a string, replacing all string s1 in string s with string s2.
LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)	The three functions have the same function, returning the starting position of the substring str1 in the string str (starting from the number of characters).
FIELD(s,s1,s2,...)	Returns the position of the first string matching the string s.

1. String stitching

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

Returns the string generated by the connection parameter, one or more contents to be spliced, and any one is NULL, the return value is NULL.

SELECT CONCAT('The present time:',NOW());  -- Output result：Time now：2019-01-17 11:27:58

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

Returns the string after multiple strings are spliced, with an x between each string.

SELECT CONCAT_WS(';','pan_junbiao','KevinPan','pan_junbiao'); -- Output result：pan_junbiaoBlog;KevinPan;pan_junbiao

2. Intercepting of strings

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

The two functions have the same function, and return a string starting from the nth character and length len from the string s.

SELECT SUBSTRING('Hello, welcome to me',8,14);  -- Output result：The me
SELECT MID('Hello, welcome to me',8,14);        -- Output result：The me

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

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

SELECT LEFT('Hello, welcome to me',7);   -- Output result：Hello，Welcome to visit
SELECT RIGHT('Hello, welcome to me',14); -- Output result：I

3. String replacement

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

Returns the string s1, whose substring starts at position x, and is replaced by the string s2.

SELECT INSERT('Hello, welcome to visit',8,2,'');  -- Output result：Hello，Welcome to visit

3.2 REPLACE(s,s1,s2) function

Returns a string, replacing all string s1 in string s with string s2.

SELECT REPLACE('Hello, welcome to me','I',''); -- Output result：Hello，Welcome to me

4. Query location of string

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 number of characters).

SELECT LOCATE('www.jb51','Hello, welcome to www.jb51 me');       -- Output result：8
SELECT POSITION('www.jb51' IN 'Hello, welcome to www.jb51 Me');  -- Output result：8
SELECT INSTR('Hello, welcome to visit','I');        -- Output result：8

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

Returns the position of the first string matching the string s.

SELECT FIELD('www.jb51','www.jb51','KevinPan','www.jb51','www.jb51me');  -- Output result：4

This is the article about splicing, intercepting, replacing, and searching MySQL strings. For more related contents of mysql strings, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!