Search a whole table in mySQL for a string

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to sql

Examples related to search