Search in all columns for a single value

Databases
1

Hi there

I am trying to add a “quick search” box to my front end that will search a table (single table) for a value that has been input across ALL columns of the table. for example this is my table

|*serial number*|*model type*|*engineers name*|
| 123456712 | X4100 | john smith |
| 410065432 | V4323 | peter jones |
| 773456888 | X4600 | james bar|

is there a way I can issue a single quesry that will search all of the columns using LIKE that will return any relevant rows
for example if i searched for the string …

4100

it would return the following two rows of the table above because that string exists within the row somewhere, as you can see in red below

|*serial number*|*model type*|*engineers name*|
| 123456712 | X[COLOR="Red"]4100[/COLOR] | john smith |
| [COLOR="Red"]4100[/COLOR]65432 | V4323 | peter jones |

Apologies if this is a really basic question ,but i think it would be great to allow someone to search for any string rather than have to select the specific column it exists within

any help would be greatly appreciated

2

thanks r937. Is specifying every column manually the only way of doing this because whilst i only gave an example table of 3 columns, in reality, I have nearly one hundred

if it is, then it is, but i thought id ask :slight_smile:

3

indeed it is :slight_smile:

4 
SELECT serial_number
     , model_type
     , engineers_name
  FROM daTable
 WHERE CAST(serial_number AS CHAR) LIKE '%4100%'
    OR model_type LIKE '%4100%'
    OR engineers_name LIKE '%4100%'
5

thanks again