Usage of Mysql string truncation function SUBSTRING

When creating the view today, it is very convenient to use string Truncation in MySQL.

When creating the view today, it is very convenient to use string Truncation in MySQL.

I feel that the string function of MySQL intercepts characters, which is more powerful than program interceptions (such as PHP or JAVA). So I want to make a record here and hope it will be useful to everyone.
Function:
1. Extract strings from the left
Left (str, length)
Note: left (truncated field, truncated length)
Example: select left (content, 200) as abstract from my_content_t
2. Extract strings from the right
Right (str, length)
Note: right (intercepted field, truncated length)
Example: select right (content, 200) as abstract from my_content_t
3. truncate a string
Substring (str, pos)
Substring (str, pos, length)
Description: substring (truncated field, starting from the nth digit)
Substring (truncated field, starting from the nth digit and intercepting the 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, for example,-5, it is the length from the last to the end or truncation of the string)
4. truncate a string by keyword
Substring_index (str, delim, count)
Note: substring_index (intercepted field, keyword, number of times the keyword appears)
Example: select substring_index ("blog.jb51.net ",". ", 2) as abstract from my_content_t
Result: blog. jb51
(Note: if the number of times a keyword appears is negative, for example,-2, it is counted from the last to the end of the string)

Function introduction:

SUBSTRING (Str,Pos), SUBSTRING (StrFROMPos) SUBSTRING (Str,Pos,Len), SUBSTRING (StrFROMPosFORLen)

Not includedLenParameter format from stringStrReturns a substring starting from positionPos. WithLenParameter format from stringStrReturns the same lengthLenSubstring with the same character, starting from positionPos. Use the FROM format as the standard SQL syntax. OrPosUse a negative value. In this case, the position of the substring startsPosCharacter rather than the starting position of the string. In the following format, you canPosUse a negative value.

For more information, see the manual.

Instance:
Table 1: user

Table 2: jl

Expected results: the id value stored in the jlid field of the user table is used to read the corresponding records in the jl table. to read the records with IDs of 1 and 2 in the jl table, first use in, but unfortunately
There are two id values stored in the jlid field. Although the format complies with the in (1, 2), if you use select jl. * from jl where jl. id in (select jlid from user where user. id = 1.

So what should we do? If we can get 1 and 2 in 1 and 2 respectively, then we can. Fortunately, mysql also provides the string truncation 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

A brief explanation:

Select substring (SELECT user. jlid FROM user WHERE user. id = 1), 1, 1 ))
Subquery is used here. First, query the value of the jlid field with id 1 in the user table, return a string, and then use SUBSTRING for truncation to obtain string 1.
Select substring (SELECT user. jlid FROM user WHERE user. id = 1), 3, 1 ))
This statement returns 2

1 and 2 get and query through the where of the primary query. Note that we need to query records with id = 1 and id = 2, so we use OR. How is it a little troublesome,

Is it your first instinct to use two SQL statements in combination with the explode function of php? This is normal, but the efficiency between the two is high, and the webmaster has not tested it. I hope you can help me!