Search a particular String in entire Mysql table(All fields)

Question

I am currently working with below query with single field search

SELECT *
FROM user_college_tbl
WHERE college_name LIKE '%Impulse%';

How to Search a particular String in entire table(All fields)?

Answer 1

You could look into using full text search:

SELECT *
FROM user_college_tbl
WHERE MATCH(col1, col2, ...) AGAINST ('Impulse' IN NATURAL LANGUAGE MODE)

This would assume that you had full text indices setup on all the text columns you wanted to be included in the search:

CREATE TABLE user_college_tbl (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    college_name VARCHAR(200),
    col1 TEXT,
    col2 TEXT,
    -- ... more text columns
    FULLTEXT(college_name, col1, col2, ...)
) ENGINE=InnoDB;

If you are using a version of MySQL earlier than 5.6 or you don't want full text search, then you may be limited to ORing together LIKE expressions for each text column in the table, e.g.

SELECT *
FROM user_college_tbl
WHERE college_name LIKE '%Impulse%' OR
      col1 LIKE '%Impulse%' OR
      col2 LIKE '%Impulse%'