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'

Answers


Try something like this:

SELECT * FROM clients WHERE CONCAT(field1, '', field2, '', fieldn) LIKE "%Mary%"

You may want to see SQL docs for additional information on string operators and regular expressions.

Edit: There may be some issues with NULL fields, so just in case you may want to use IFNULL(field_i, '') instead of just field_i

Case sensitivity: You can use case insensitive collation or something like this:

... WHERE LOWER(CONCAT(...)) LIKE LOWER("%Mary%")

Just search all field: I believe there is no way to make an SQL-query that will search through all field without explicitly declaring field to search in. The reason is there is a theory of relational databases and strict rules for manipulating relational data (something like relational algebra or codd algebra; these are what SQL is from), and theory doesn't allow things such as "just search all fields". Of course actual behaviour depends on vendor's concrete realisation. But in common case it is not possible. To make sure, check SELECT operator syntax (WHERE section, to be precise).


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.


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')

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!!


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.


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.


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


Need Your Help

How to get session remember after login in laravel 5?

php session laravel-5

I start to work with my project using laravel 5. I realized that it work fine in my local directory with session after I login to my site, But I just know that I got problem when I hosted my projec...

jquery mouse position relative window

position jquery

I'm trying to get the exact mouse position relative to the window.