MySQL SUBSTRING character intercept 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 Forlen)

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!