MySQL handles String functions

Http://www.jb51.net/article/27458.htm

It feels like MySQL's string function intercepts characters, and is more powerful than a program intercept (like PHP or Java), so make a record here and hope it works for everyone.
Function:
1. Intercept string from left
Left (str, length)
Description: Left (truncated field, intercept length)
Example: Select Left (content,200) as abstract from my_content_t
2, starting from the right to intercept the string
Right (str, length)
Description: Right (truncated field, intercept length)
Example: Select Right (content,200) as abstract from my_content_t
3. Intercepting strings
SUBSTRING (str, POS)
SUBSTRING (str, pos, length)
Description: Substring (intercepted field, starting from the first intercept)
SUBSTRING (intercepted field, starting from the first intercept, 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, 5 is the number of digits from the back to the end of the string or the length of the Intercept)
4. Intercept strings by keyword
Substring_index (Str,delim,count)
Description: Substring_index (the number of occurrences of the intercepted field, keywords, keywords)
Example: Select Substring_index ("Blog.jb51.net", ". ", 2) as abstract from my_content_t
Results: blog.jb51
(Note: If the number of occurrences of a keyword is negative, 2 is the inverse from the back 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 the string str, starting at position pos. The format with the Len parameter returns a substring of the same length as the Len character from the string str, starting at position pos. Use the from format as standard SQL syntax. You may also 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 the POS in the following format function.

Please refer to the manual for details.

Instance:
Table 1:user

Table 2:JL

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

So what do we do? If we were able to get 1 and 2 respectively, we could. Fortunately, MySQL also provides 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

Simply explain:

Select SUBSTRING ((select user.jlid from user WHERE user.id =1), 1, 1))
Using a subquery here, we first query the value of the Jlid field in the user table with ID 1, return a string, and then intercept it using substring to 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, to note that we need to query id=1 and id=2 records, so the use of or, how, is not a little trouble,

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

MySQL handles string functions (GO)