Shulou(Shulou.com)06/01 Report--

I encountered a need to intercept a specific character in a string in the field of MySql, which is similar to the interception of regular expressions. Due to the lack of a suitable method, Baidu finally found a suitable method: substring_index ('www.sqlstudy.com.cn','.',-2)

This method is highly recommended to obtain data containing specific characters.

Substring_index (input,split,index): input is the character to be intercepted, split is the delimiter, and Index is the string to be truncated to the left (positive index) or right (negative index) of the index delimiter.

Take a personal string as an example: 'Provider= "RiskManagement" finalScore= "65" RGID= "100397278" I want to get the value of finalScore:

-- 1-get the character select substring_index on the right side of finalScore ('Provider= "RiskManagement" finalScore= "65" RGID= "100397278"', 'finalScore= ",-1);-- 2-get the character select substring_index on the left side of" RGID= "(substring_index (' Provider=" RiskManagement "finalScore=" 65 "RGID=" 100397278 ", 'finalScore=",-1),' RGID= ", 1)

Result 1:65 "RGID=" 100397278 "

The result is 2:65

MySQL string intercepting functions: left (), right (), substring (), substring_index (). And mid (), substr (). Among them, mid (), substr () is equivalent to the substring () function, substring () is very powerful and flexible.

1. String interception: left (str, length)

Mysql > select left ('sqlstudy.com', 3); +-+ | left (' sqlstudy.com', 3) | +-+ | sql | +-+

two。 String interception: right (str, length)

Mysql > select right ('sqlstudy.com', 3); +-+ | right (' sqlstudy.com', 3) | +-+ | com | +-+

3. String interception: substring (str, pos); substring (str, pos, len)

3.1 start at the fourth character position of the string and end.

Mysql > select substring ('sqlstudy.com', 4); +-+ | substring (' sqlstudy.com', 4) | +-+ | study.com | +-+

3.2 start at the fourth character position of the string and take only 2 characters.

Mysql > select substring ('sqlstudy.com', 4,2) +-- + | substring ('sqlstudy.com', 4,2) | +-+ | st | +-+

3.3 start at the 4th character position (reciprocal) of the string and end it.

Mysql > select substring ('sqlstudy.com',-4); +-- + | substring (' sqlstudy.com',-4) | +-- + | .com | +-+

3.4 start with the fourth character position (reciprocal) of the string and take only 2 characters.

Mysql > select substring ('sqlstudy.com',-4,2) +-- + | substring ('sqlstudy.com',-4,2) | +-+ | .c | +-+

We notice that in the function substring (str,pos, len), pos can be negative, but len cannot be negative.

4. String interception: substring_index (str,delim,count)

4.1 intercept the second'.' All the previous characters.

Mysql > select substring_index ('www.sqlstudy.com.cn','. 2) +-- + | substring_index ('www.sqlstudy.com.cn','.' 2) | +-+ | www.sqlstudy | +-- +

4.2 intercept 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 cannot be found in the string, the entire string is 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 field data in a table if the field data is 1pm 2pm 3

Mysql > select substring_index (substring_index (this field,',', 2),',',-1) from table name; +-- + | substring_index (substring_index (this field,', 2) ,',' -1) | +-+ | 2 | + -+

Summary

The above is the Mysql string interception and data acquisition in the specified string introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.