If you wish to search for an entry in a table in a MySQL database that contains a text string, such as "Groot", you can use the
LIKE operator.
E.g., for a table named chatroommessages with a a field named
message, you could search for any occurrences of "Groot" in
a message with the following:
SELECT `message` FROM `chatroommessages` WHERE `message` LIKE '%Groot%';
The percent signs at the beginning and end of the text indicate that any other text can occur before and after that text, so if a message contained "I am Groot!", it would be selected. The "%" will match any number of characters, including zero characters.
You can search for words or phrases with any other text occurring before or after the text you are seeking:
SELECT `message` FROM `chatroommessages` WHERE `message` LIKE '%only a test%';
If you want to use a wildcard that represents only one character rather than zero or more characters, you can use the underscore character.
SELECT `message` FROM `chatroommessages` WHERE `message` LIKE '%test_r%';
The above SQL query would look for all messages that contained any text before "test", followed by any one character, an "r", and then any number of other characters. So the above query would find any of the following:
Hopefully I don't run out of Testor's Dull coat.
my test results were inconclusive.
I am currently a beta tester.
The case of the text doesn't matter. E.g., it doesn't matter that I used all lowercase letters in "test" when I issued the SQL query. The query would find "TEST" with all uppercase letters as well. So the message contained "Testor" with a capital "T" was also found.
If you need to search for an occurrence of a wildcard character, e.g.,
you need to find "30%", then you would need to use a backslash, \,
as an "escape
character" to take away the special meaning of the percent sign in a query.
SELECT `message` FROM `chatroommessages` WHERE `message` LIKE '30\%';
That will restrict the search to finding only messages containing "30%",
whereas if you used LIKE 30% rather than LIKE 30\%,
the search would also return any messages containing "300", "3000", "30132",
etc.
| String | Description |
|---|---|
| \% | Matches one “%” character |
| \_ | Matches one “_” character |
References:
