How to query contained strings by mysql

This article mainly introduces mysql how to query the included strings, the article is very detailed, has a certain reference value, interested friends must read it!

Methods: 1, using "table name where field like'% string%'" query; 2, using "table name where find_in_set (string, field)" query; 3, using "table name where locate (string, field)" query.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How does mysql query the contained strings

Summarize three ways to determine whether a string contains a string in MySQL.

Let's start with a simple scenario, such as querying the records of yanggb in the user table.

Method 1: use the wildcard%.

Wildcard, that is, fuzzy matching, can be divided into leading fuzzy query, post-guided fuzzy query and full-guided matching query, which is suitable for the scenario of querying whether a string contains another fuzzy query.

Select * from user where hobby like'% yanggb%'

The scenario used is limited to finding records where yanggb exists in hobby (hobby is multiple values separated by commas), and vice versa.

Method 2: use the string function find_in_set () provided by MySQL.

MySQL provides a string function find_in_set (str1,str2) function that returns the index of the location of str1 in str2. If it is found, it returns true (1), otherwise it returns false (0), where str2 must be separated by the comma [,] of the half-width symbol.

Select * from user where find_in_set ('yanggb', hobby)

When a matching string is used as the first parameter, the applicable scenario is to find records where yanggb exists in the hobby (hobby is multiple values separated by commas).

Select * from user where find_in_set (hobby, 'yanggb1,yanggb2,yanggb3')

When the matching string is used as the second parameter, the applicable scenario is to find a record with one of yanggb1, yanggb2, and yanggb3 in the hobby (hobby is a single value).

Method 3: use the string function locate () function provided by MySQL.

MySQL also provides a string function locate (substr,str) function, which returns the index of the location of substr in str. If found, it returns a number greater than 0, otherwise it returns 0.

Select * from user where locate ('yanggb', hobby) > 0

The applicable scenario is similar to the find_in_set () function, and the only difference between the two functions is probably the return value.

The above is all the content of the article "how to query the included strings in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!