Using LIKE with multiple columns in a table

Question

There are three, possible four columns in the table that I will need to use.

year
playerName
variation

Lets say a user type in 2000 Matt Base. 2000 is the year, Matt is the player name, and base would be variation. However, what if another use types in Matt Base. And another 2000 Matt, or 2000 Base.
I know I can do something like select * from TBL where name LIKE '%$input%'
How can I search three columns based on a single user input? maybe explode it at the space and search each section? Is there a better way?

Tim Biegeleisen · Accepted Answer

Now you may search the description column based on some user input:
SELECT *
FROM yourTable
WHERE description REGEXP ?

To the ?, you may bind the search term, surrounded in word boundaries.  For example, to search for Matt among the description, bind:
"[[:<:]]Matt[[:>:]]"

ysth · Answer

If you want the search terms for come in any order, you can do
where concat(year, playerName, variation, year, playerName, variation, year) like concat('%',replace(?,' ','%'),'%')

Bhargav Variya · Answer

You can use OR Operator in WHERE condition for get data from multiple column.
Your query like
select * from TBL where name LIKE '%$input%' OR playerName LIKE '%$input%' OR variation 
LIKE '%$input%'

You can also set AND operator according to your requirement.

Using LIKE with multiple columns in a table

3 Answers

Add your own answers!

Ask a Question