MySQL string interception functions: left(), right(), substring(), substring_index(). There are mid(), substr(). Among them, mid() and substr() are equivalent to the substring() function, and the function of substring() is very powerful and flexible. 1. String interception: left(str, length) mysql> select left(‘sqlstudy.com’, 3); +———————– –+ | left(‘sqlstudy.com’, 3) | +————————-+ | sql | +—- ———————+ 2. String interception: right(str, length) mysql> select right(‘sqlstudy.com’, 3); +- ————————-+ | right(‘sqlstudy.com’, 3) | +———– —————+ | com | +————————–+ 3. Characters String interception: substring(str, pos); substring(str, pos, len) 3.1 Take from the 4th character position of the string until the end. mysql> select substring(‘sqlstudy.com’, 4); +——————————+ | substring(‘ sqlstudy.com’, 4) | +——————————+ | study.com | +—- ————————–+ 3.2 Take from the 4th character position of the string, only take 2 characters. mysql> select substring(‘sqlstudy.com’, 4, 2); +——————————— + | Substring(‘sqlstudy.com’, 4, 2) | +———————————+ | st | +———————————+ 3.3 From the 4th character position of the string (countdown ) Start to fetch until the end. mysql> select substring(‘sqlstudy.com’, -4); +——————————-+ | substring (‘sqlstudy.com’, -4) | +————————-+ | .com | .com | +- ——————————+ 3.4 Take from the 4th character position (the countdown) of the string, only take 2 Characters. mysql> select substring(‘sqlstudy.com’, -4, 2); +——————————– –+ | substring(‘sqlstudy.com’, -4, 2) | +——————————- —+ | .c We noticed that in the function substring In (str, pos, len), pos can be a negative value, but len cannot be a negative value. 4. String interception: substring_index(str,delim,count) 4.1 Intercept all characters before the second ‘.’. mysql> select substring_index(‘www.sqlstudy.com.cn’,’.’, 2); +————————— ———————+ | substring_index(‘www.sqlstudy.com.cn’,’.’, 2) | +——- —————————————–+ | www.sqlstudy | +– ———————————————-+ 4.2 Interception All characters after the second’.’ (countdown). mysql> select substring_index(‘www.sqlstudy.com.cn’,’.’, -2); +————————– ———————–+ | substring_index(‘www.sqlstudy.com.cn’,’.’, -2) | +—- ———————————————+ com.cn | +———————————————— -+ 4.3 If the value specified by the delim parameter is not found in the string, the entire string will be returned mysql> select substring_index(‘www.sqlstudy.com.cn’,’.coc’, 1); +—- ———————————————–+ | substring_index (‘www.sqlstudy.com.cn’,’.coc’, 1) | +—————————— ———————+ | www.sqlstudy.com.cn | +—————— ———————————+ 4.4 Intercept the intermediate value of a certain field of a table, if the field data is 1,2,3 mysql> select substring_index(substring_index(the field,’,’, 2),’,’, -1) from table name; +—————- ———————————————-+ | substring_index( substring_index(the field,’,’, 2);,’,’, -1)| +—————————- ———– ———————–+ | 2 | +———————- —————————————-+
< p>