Checking If a String Is Found in One of Multiple Columns in MySQL

Checking if a string is found in one of multiple columns in mySQL

Can't you just use separate WHERE clauses, such as:

SELECT * FROM tblClients
WHERE (name LIKE '%john%')
OR (surname LIKE '%john%')

You're having to amend the SQL based on which columns the user selects anyway.

Check if string is found in one of multiple columns in SQL

Actually I think that separate logical checks in the WHERE clause for each column is the way to go here. If you can't do that for some reason, consider using a WHERE IN (...) clause:

SELECT *
FROM Items
WHERE '6922896068701' IN (Code, Code1);

If instead you want LIKE logic, then it gets tricky. If you knew that the matching codes would always consist of numbers/letters, then you could try:

SELECT *
FROM Items
WHERE ',' + Code + ',' + Code1 + ',' LIKE '%,6922896068701,%';

Checking if a multi string is found in one of multiple columns in mySQL

You can keep it simple and do:

select * from myTable WHERE name LIKE '%stack%' OR 
                            name LIKE '%over%' OR
                            name LIKE '%flow%';

or:

select * from myTable WHERE name LIKE '%stack%' AND
                            name LIKE '%over%' AND
                            name LIKE '%flow%';

This doesn't look as nice as the REGEXP from Young, but it doesn't have the unexpected behaviour REGEXP can have given that user input can be anything. With REGEXP you really need to know what you're doing.

How to search LIKE string value in multiple columns?

This (python) bit gives me the results I want.

    columns = ["col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8"]
    sql = "SELECT * FROM table WHERE col0 LIKE '%"+_name+"%'"
    for column in columns:
        sql += "OR "+column+" LIKE '%"+_name+"%s'"
    sql += ";"

There must be a better way but this works for now.

MySQL: Look for the same string in multiple columns

Simple workaround:

SELECT * 
FROM projects 
WHERE 
    CONCAT(category,name,description,keywords,type) LIKE '%query%' 
ORDER BY name ASC;

You can add separators between columns if needed:

SELECT * 
FROM projects 
WHERE 
    CONCAT(category,"|",name,"|",description,"|",keywords,"|",type) LIKE '%query%' 
ORDER BY name ASC;

You can also use a fulltext search (you need to create a fulltext index as described here: How do FULLTEXT INDEXES on multiple columns work?)

SELECT *, MATCH (category,name,description,keywords,type) AGAINST ('query') AS score FROM projects WHERE MATCH (category,name,description,keywords,type) AGAINST ('query');

Checking multiple columns for one value

You can use the IN predicate, like so:

SELECT * FROM table WHERE 123 IN(col1, col2, col3, col4);

SQL Fiddle Demo

---

it's the opposite version of IN.

No it is not, It is the same as using the ORs the way you did in your question.

---

To clarify this:

The predicate IN or set membership is defined as¹:

Where the Value Expression can be either ²:

So it is fine to do it this way, using the value expression 123, which is a literal.

---

^{1, 2: Images from: SQL Queries for Mere Mortals(R): A Hands-On Guide to Data Manipulation in SQL}

---

Related Topics