[mysql] Search a whole table in mySQL for a string

I'm trying to search a whole table in mySQL for a string.

I want to search all fields and all entrees of a table, returning each full entry that contains the specified text.

I can't figure out how to search multiple fields easily; here are the details:

The table is "clients". It has about 30 fields and 800 entries, too much to show all at once in a browser. I would like to search for a name (i.e. "Mary"), but it could be in the shipping_name field or the billing_name field, or the email field, etc.

I would like to search all fields for any entries that contain the string "Mary". This is what I think should work but doesn't:

SELECT * FROM `clients` IN 'Mary'

A PHP Based Solution for search entire table ! Search string is $string . This is generic and will work with all the tables with any number of fields

$sql="SELECT * from client_wireless";
$sql_query=mysql_query($sql);
$logicStr="WHERE ";
$count=mysql_num_fields($sql_query);
for($i=0 ; $i < mysql_num_fields($sql_query) ; $i++){
 if($i == ($count-1) )
$logicStr=$logicStr."".mysql_field_name($sql_query,$i)." LIKE '%".$string."%' ";
else
$logicStr=$logicStr."".mysql_field_name($sql_query,$i)." LIKE '%".$string."%' OR ";
}
// start the search in all the fields and when a match is found, go on printing it .
$sql="SELECT * from client_wireless ".$logicStr;
//echo $sql;
$query=mysql_query($sql);

If you are just looking for some text and don't need a result set for programming purposes, you could install HeidiSQL for free (I'm using v9.2.0.4947).

Right click any database or table and select "Find text on server".

All the matches are shown in a separate tab for each table - very nice.

Frighteningly useful and saved me hours. Forget messing about with lengthy queries!!

In addition to pattern matching with 'like' keyword. You can also perform search by using fulltext feature as below;

SELECT * FROM clients WHERE MATCH (shipping_name, billing_name, email) AGAINST ('mary')

If you're using Sublime, you can easily generate hundreds or thousands of lines using Text Pastry in conjunction with multiple line selection and Emmet.

So in my case I set the document type to html, then typed div*249, hit tab and Emmet creates 249 empty divs. Then using multiple selection I typed col_id_ in each one and triggered Text Pastry to insert an incremental id number. Then with multiple selection again you can delete the div markup and replace it with the MySQL syntax.

Try this code,

SELECT 
       * 
FROM 
       `customers` 
WHERE 
       (
          CONVERT 
               (`customer_code` USING utf8mb4) LIKE '%Mary%' 
          OR 
          CONVERT(`customer_name` USING utf8mb4) LIKE '%Mary%' 
          OR 
          CONVERT(`email_id` USING utf8mb4) LIKE '%Mary%' 
          OR 
          CONVERT(`address1` USING utf8mb4) LIKE '%Mary%' 
          OR
          CONVERT(`report_sorting` USING utf8mb4) LIKE '%Mary%'
       )

This is help to solve your problem mysql version 5.7.21

for specific requirement the following will work for search:

select * from table_name where (column_name1='%var1%' or column_name2='var2' or column_name='%var3%') and column_name='var';

if you want to query for searching data from the database this will work perfectly.

Identify all the fields that could be related to your search and then use a query like:

SELECT * FROM clients
WHERE field1 LIKE '%Mary%'
   OR field2 LIKE '%Mary%'
   OR field3 LIKE '%Mary%'
   OR field4 LIKE '%Mary%'
   ....
   (do that for each field you want to check)

Using LIKE '%Mary%' instead of = 'Mary' will look for the fields that contains someCaracters + 'Mary' + someCaracters.