How to Check if String Contains Certain Data in MySQL

To find rows whose text contains a substring in MySQL, filter with WHERE LOCATE('widget', product_name) > 0, WHERE INSTR(product_name, 'widget') > 0, or WHERE product_name LIKE '%widget%'. LOCATE() and INSTR() test for a literal substring; LIKE is useful when the search needs wildcards. The examples below use the same small products table so you can choose the form that fits your query.

Set up a sample table for substring searches

Create a table with an identifier and a product name, then add a few rows that include and omit the search term. In a MySQL session, the setup can be written as CREATE TABLE products (product_id INT PRIMARY KEY, product_name VARCHAR(255)); followed by INSERT INTO products (product_id, product_name) VALUES (1, 'Gadget Widget'), (2, 'Electronic Widget'), (3, 'Toy Widget'), (4, 'Tool Set');.

This dataset has three names containing Widget and one name that does not. The following queries select the first three rows. The SQL snippets are intended to run in MySQL; no MySQL service execution evidence is supplied with this article task, so their results are described rather than shown as observed MySQL command output. In your own database, run the table definition and insert once before trying the searches. If a table named products already exists, use a different table name or adapt the setup to your schema.

This local Python check mirrors the sample’s expected matches; it does not execute MySQL. It prints the product names containing Widget.

products = [
    (1, "Gadget Widget"),
    (2, "Electronic Widget"),
    (3, "Toy Widget"),
    (4, "Tool Set"),
]

for product_id, product_name in products:
    if "Widget" in product_name:
        print(product_id, product_name)

1 Gadget Widget
2 Electronic Widget
3 Toy Widget

Check for a substring with LOCATE() and INSTR()

Use LOCATE() when the search term is the first argument and the column value is the second: LOCATE(substring, string). It returns the position of the first match, starting at 1, or 0 when there is no match. In a WHERE condition, comparing the result with > 0 keeps only rows where the substring occurs. For this table, the query is SELECT product_id, product_name FROM products WHERE LOCATE('Widget', product_name) > 0;. It matches IDs 1, 2, and 3.

INSTR() tests the same values but reverses the argument order: INSTR(string, substring). It also returns the first match position or 0 if the substring is absent. The equivalent query is SELECT product_id, product_name FROM products WHERE INSTR(product_name, 'Widget') > 0;. This returns the same three rows. Remembering which argument comes first is the key difference when switching between the two functions.

Both functions search for the supplied substring within the column value. They are useful when the term is a literal value you already have. The result is a position, not a row; the WHERE predicate turns that numeric result into a row filter. Select only the columns your application needs in place of * to make the result easier to use.

How a comparison treats letter case depends on the collation used by the column or expression. The sample queries use Widget to match the sample capitalization. Check your column’s collation when results differ by case from what you expect; do not assume every MySQL comparison has identical case behavior.

Match substrings and patterns with LIKE

The LIKE operator compares a column value with a pattern. Without wildcard characters, the pattern is compared as a whole value: product_name LIKE 'Widget' looks for a value matching Widget, rather than searching for those letters anywhere inside a longer name. To find the term anywhere in the value, add % on both sides: SELECT product_id, product_name FROM products WHERE product_name LIKE '%Widget%';. This contains pattern matches IDs 1, 2, and 3 in the sample table.

The % wildcard stands for zero or more characters. Put it before a term to match values ending with that term, as in product_name LIKE '%Widget'; put it after the term to match values beginning with it, as in product_name LIKE 'Toy%'. Put % on both sides to allow characters before and after the term. These placements let you describe where the text should occur without spelling out the surrounding characters.

The _ wildcard stands for exactly one character. For example, product_name LIKE 'Gadget_Widget' matches a value with Gadget, then one character, then Widget; in this dataset it matches Gadget Widget, where the intervening character is a space. % and _ have different roles: the first allows a sequence of any length, while the second allows one character at that position.

Choose LIKE when a pattern expresses the search you need, and use LOCATE() or INSTR() for a direct substring check. For literal searches that include % or _ as ordinary characters, escape them according to the MySQL LIKE escape rules instead of leaving them as pattern wildcards. These predicates filter rows; they do not validate user input. If a search term comes from a user, pass it as a parameter through your database client and handle it according to that client’s parameter-binding guidance.