Need help constructing MySQL query ...

[johnkelly]

If I have 2 database records that contain only the string 'dog' in one of the columns and I want to construct a query that retrieves those two records based on the precense of 'dog' in a string like 'big dog bite' what would the query look like? For example:

SELECT * FROM TABLE WHERE [what goes here]

[YappyDog]

SELECT * FROM TABLE WHERE myColumn like '%dog%'

or, if you want it to be case-insensitive

SELECT * FROM TABLE WHERE lower(myColumn) like '%dog%'

The percent sign (%) is treated as a wildcard, so it says give me rows where the column contains anything, then dog, then anything.

[johnkelly]

Thanks for the reply I guess I did not explain it right. Let me try again, I want to find all records that contain 'dog' based on a user inputted string like 'big dog bite'. I do not mind getting back results that contain 'big' or 'bite' as well but they must have 'dog' Is this possible?

[YappyDog]

How do you know which of the words they enter is the one that MUST be in the results?

[johnkelly]

Whoops, the results do not have to have 'dog' in them. So the query should return all results that contain any one of the terms.

So if someone enters 'big dog bite' it should return all records that contain 'dog', 'big' or 'bite'. I guess I could explode the inputed string by spaces and then do multiple queries on each term or is there a way to do this with one query?

[YappyDog]

SELECT * FROM TABLE
WHERE myColumn like '%big%'
or myColumn like '%dog%'
or myColumn like '%bite%'

[johnkelly]

Thank you 🙂