MySQL string truncation and splitting function usage example

MySQL string truncation and splitting function usage example

This example describes how to use the string functions of MySQL to intercept and split. We will share this with you for your reference. The details are as follows:

FirstString truncation function:

SUBSTRING(commentid,9)

This is very simple, starting from 9th characters to the end. There are three SUBSTRING parameters. The last one is the truncation length. The default value is to the end, and the negative value is the last digit.

NextSplit string functions:

SUBSTRING_INDEX(commentid, '-', 1)

This is a little more complicated. It means to split the string and retrieve all the strings from the first keyword. If the third parameter is changed to-1, the last parameter is used. What if we want to extract 50 or 11065 from the string c-11065-50?

Obtain 50 statements:
Copy codeThe Code is as follows: SELECT SUBSTRING_INDEX (checkid, '-',-1) FROM 'check' WHERE checkid = 'C-11065-50'
Write 11065 of the results:
Copy codeThe Code is as follows: SELECT SUBSTRING_INDEX (checkid, '-',-2), '-', 1) FROM check WHERE checkid = 'C-11065-50'
Or:
Copy codeThe Code is as follows: SELECT SUBSTRING_INDEX (checkid, '-', 2), '-',-1) FROM check WHERE checkid = 'C-11065-50'
It looks complicated, so it's more complicated:

This below is the combination of usage, for example we want to intercept: 13 in the content_13-11220-1, the simplest is:
Copy codeThe Code is as follows: SELECT SUBSTRING_INDEX (SUBSTRING (commentid, 9), '-', 1), '-',-1) FROM check WHERE commentid = 'content _ 13-11220-1'
We found that three functions need to be called here. Can we call them twice. So we can write:
Copy codeThe Code is as follows: SELECT SUBSTRING_INDEX (commentid, '-', 1), '_',-1) FROM check WHERE commentid = 'content _ 13-11220-1'
In this way, the Merge function can be executed less. When we run enough data, the speed will be obvious.

MySQL more functions can refer to MySQL functions Daquan: http://www.bkjia.com/article/42906.htm