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 · 2020-09-18 04:18:07Z

2

If you need to search all three columns in one logical step, as text, then I might suggest actually just maintaining a fourth column as such:

year | playerName | variation | description
2000 | Matt       | Base      | 2000 Matt Base

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[[:>:]]"

answered Sep 18, 2020 at 4:18

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

letsCode Over a year ago

I guess I can do that, but i also have about 300 entries in the table. would need to figure out a way to combine the current. i like this idea though.

Tim Biegeleisen Over a year ago

@soldfor Then see the other answer for the alternative. You would have to search each column using separate logic.

letsCode Over a year ago

i was able to use the concat function and have it do it for me. from there i can use the LIKE. thanks so much for make me use my noggin

Tim Biegeleisen Over a year ago

Sure, that should work. My answer uses a regex search, which you also might want to consider to avoid false substring matches.

letsCode Over a year ago

first time doing the regex, but, card REGEXP '[[:<:]]$s[[:>:]]'"; this breaks the code. if i do single quotes around the $s, the api 200s, but the sql is wrong. what am i missing?

|

Bhargav Variya · Accepted Answer · 2020-09-18 04:16:17Z

0

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.

answered Sep 18, 2020 at 4:16

Bhargav Variya

7851 gold badge11 silver badges20 bronze badges

Comments

ysth · Accepted Answer · 2020-09-18 07:48:06Z

0

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(?,' ','%'),'%')

answered Sep 18, 2020 at 7:48

ysth

99.1k6 gold badges126 silver badges220 bronze badges

2 Comments

letsCode Over a year ago

how does the second concat work? the replace(?,' ', '%')? Im guessing the ? is the search term... but from there, you lost me.

ysth Over a year ago

yes, ? is the search term. I was makiing it so e.g. "2000 Matt Base" does like "%2000%Matt%Base%" (adding % to the beginning and end and changes spaces to %)

Collectives™ on Stack Overflow

Using LIKE with multiple columns in a table

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related