Usage instructions for Mysql string intercept function substring _mysql

The sense that MySQL's string function intercepts characters is more powerful than using program interception (such as PHP or Java), so make a record here and hope to be useful to everyone.
Function:
1. Intercept string from left
Left (str, length)
Description: Left (intercepted field, intercept length)
Example: Select Left (content,200) as abstract from my_content_t
2, from the right start to intercept the string
Right (str, length)
Description: Right (intercepted field, intercept length)
Example: Select Right (content,200) as abstract from my_content_t
3, intercept the string
SUBSTRING (str, POS)
SUBSTRING (str, pos, length)
Description: Substring (intercepted field, starting from the first few)
substring (truncated field, intercept from first, intercept length)
Example: Select substring (content,5) as abstract from my_content_t
Select substring (content,5,200) as abstract from my_content_t
(Note: If the number of digits is negative such as-5 is the number of digits from the back, to the end of the string or the length of the Intercept)
4, by keyword intercept string
Substring_index (Str,delim,count)
Description: Substring_index (truncated fields, keywords, number of keyword occurrences)
Example: Select Substring_index ("Blog.jb51.net", ". ", 2) as abstract from my_content_t
Result: blog.jb51
(Note: If the number of occurrences of the keyword is negative such as-2 is from the bottom to the end of the string)

Introduction to Functions:

SUBSTRING (str,pos), SUBSTRING ( str from pos) SUBSTRING (str,POS ,len), SUBSTRING (str from pos for len)

The format without the len parameter returns a substring from string str , starting at position Pos. Format with len parameter returns a substring of the same length as len character from string str , starting at position Pos. Use the from format as the standard SQL syntax. It is also possible to use a negative value for the POS . If so, the position of the substring starts at the POS character at the end of the string, not at the beginning of the string. You can use a negative value for a pos in a function in the following format.

Please refer to the manual for details.

Instance:
Table 1:user

Table 2:JL

Expected effect: The ID value stored in the User table Jlid field, read the corresponding record in the JL table, here want to read, JL table ID 1, 2 of the record, first thought with in, but unfortunately because
The Jlid field stores an ID value of 2, although formally conforming to the in (1,2) format, but if you are querying using select jl.* from JL where Jl.id in (select Jlid from user where user.id=1), No, he always returns a record with ID 1.

So what do we do? If we were able to get 1 and 2 of the 1,2, we could do it. Fortunately, MySQL also provides a string intercept function substring.

The SQL syntax is as follows:
SELECT JL. *
From JL
WHERE jl.id = (
SELECT SUBSTRING (

SELECT User.jlid
From user
WHERE user.id =1
), 1, 1)
OR jl.id = (

SELECT SUBSTRING (

SELECT User.jlid
From user
WHERE user.id =1
), 3, 1)
)
LIMIT 0, 30

Briefly explain:

Select SUBSTRING ((select user.jlid from user WHERE user.id =1), 1, 1)
Here is a subquery, first query the user table, the value of the Jlid field ID 1, return the string, and then use substring to intercept, get the string 1
Select SUBSTRING ((select user.jlid from user WHERE user.id =1), 3, 1)
This statement gets 2

1 and 2 have been again through the main query where to query, pay attention to We need to query id=1 and id=2 records, so use or, how, is not a bit of trouble,

Your first instinct is not to use 2 SQL statements, the middle again with PHP's explode function to query it? This thought is normal, but the two between the efficiency of high, stationmaster did not test, hope that you can help Oh!