Search all columns of a table using a single where condition with single keyword in mysql

by 
SELECT * FROM `some_table`
WHERE
CONCAT_WS('|',`column1`,`column2`,`column3`,`column4`,`column64`) # single condition, many columns
LIKE '%VT%'

Voila.

The ‘|’ separator, by the way, is to prevent you finding coincidental matches where, e.g., column1 ends in ‘V’ and column2 starts with ‘T’, which would give you a false positive in a search for “VT”.

I’m not sure if the above method is faster than the OR method (I would guess they’re the same speed) , but it definitely involves less typing if you’re writing the query by hand.

More Related Contents:

  1. MySQL search and replace some text in a field
  2. If two columns are 1 then make new column
  3. How can I return pivot table output in MySQL?
  4. Using LIMIT within GROUP BY to get N results per group?
  5. Retrieving the last record in each group – MySQL
  6. Cast from VARCHAR to INT – MySQL
  7. Required to join 2 tables with their FKs in a 3rd table
  8. Does COUNT(*) always return a result?
  9. How to change the CHARACTER SET (and COLLATION) throughout a database?
  10. Connect By Prior Equivalent for MySQL
  11. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  12. How to convert all tables from MyISAM into InnoDB?
  13. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  14. SQL Query to show nearest date?
  15. How to create relationships in MySQL
  16. Getting all parent rows in one SQL query
  17. Select from one table where not in another
  18. MySQL Select last 7 days
  19. How to select the most recent set of dated records from a mysql table
  20. Second SELECT query if first SELECT returns 0 rows
  21. Set value to NULL in MySQL
  22. SELECT query return 1 row from each group
  23. MySql Single Table, Select last 7 days and include empty rows
  24. mysql SQL: specific item to be first and then to sort the rest of the items
  25. How to retrieve JSON data from MySQL?
  26. When is a timestamp (auto) updated?
  27. MySQL OPTIMIZE all tables?
  28. Strip last two characters of a column in MySQL
  29. What happens when I exhaust a bigint generated key? How to handle it?
  30. is there a length limit to group_concat or another reason why it would not work on a text field

Leave a Comment