How to Search All Columns in a Table

How can I search all columns in a table?

SELECT ...
FROM yourtable
WHERE 'val' IN (field1, field2, field3, field4, ...)

if you're looking for exact full-field matches. If you're looking for substring matches, you'll have to go about it the long way:

WHERE field1 LIKE '%val%' or field2 LIKE '%val%' etc....

SQL search all columns of a table for text value

To do it without a special procedure in a simple statement, you could convert each row to XML and then use an XQuery on the XML to search for any value in the row that matches. So for example:

declare @SearchValue as varchar(20)
set @SearchValue = 'MyValue'

select *
    --,(select MyTable.* for XML PATH (''),TYPE) AllColumns
    --,convert(nvarchar(255),(select MyTable.* for XML PATH (''),TYPE).query('for $item in * where $item=sql:variable("@SearchValue") return $item')) FoundColumns
from MyTable
where convert(nvarchar(255),(select MyTable.* for XML PATH (''),TYPE).query('for $item in * where $item=sql:variable("@SearchValue") return $item'))<>''

A procedure specifically designed for this task could probably do this more efficiently and could take advantage of indexes... etc. Honestly I would not put this into a production database solution without quite a bit of consideration, but as a throw together search tool it's not bad. I ran a search on a 700,000 record table in 40 seconds. However if I filter by each column individually it runs nearly instantly. Also a few more caveats:

• None of the table columns can not have spaces or other unfriendly
  characters for an XML tag. I couldn't figure out how to get column names with spaces to work. Maybe there's a way.
• The filter has to be written in XQuery... which is not exactly like
  SQL. But you can use =, <, >, and there's even pattern matching.
• The parameter for the query function must be a string literal. So
  you can't build a string dynamically. This is why I used the variable for your search values, but you could also use a sql:column("ColName") if needed.
• If searching for other types besides strings, the search string you use must match exactly what the field would be converted to as an XML value.

Search for Value in all Columns in SQL Table

There is no shortcut for this, you have to specify the list of columns you wish to search.

SELECT * 
FROM table_name 
WHERE column1 LIKE '%search_key%' OR 
      column2 LIKE '%search_key%' OR  
      column3 LIKE '%search_key%' 

Or create metadata for all the columns into a single column and use like on the metadata column

Search keywords on all column of the table

You can put multiple LIKE conditions in the WHERE clause that check every condition you named. According to your description, this query will work:

SELECT vehicle_id, vehicle_major_part_number, vehicle_sub_part_number
FROM vehicle_parts
WHERE vehicle_major_part_number LIKE '11111%'
OR vehicle_major_part_number LIKE '88888%'
OR vehicle_sub_part_number LIKE '11111%'
OR vehicle_sub_part_number LIKE '88888%';

Queries with LIKE and especially with % (to mark further characters might follow) are often slow and as you see, they can be long and bad to read, too.
You could also try something like this:

SELECT vehicle_id, vehicle_major_part_number, vehicle_sub_part_number
FROM vehicle_parts
WHERE CONCAT(vehicle_major_part_number, vehicle_sub_part_number) LIKE '%11111%' OR 
CONCAT(vehicle_major_part_number, vehicle_sub_part_number) LIKE '%88888%';

This might be a bit better to read, but still slow.

If you do exactly know which string you want to check, this will make your query much shorter, easier to read and will improve the execution time.
As example:

SELECT vehicle_id, vehicle_major_part_number, vehicle_sub_part_number
FROM vehicle_parts
WHERE '11111 xxx' IN (vehicle_major_part_number, vehicle_sub_part_number)
OR '88888 zzz' IN (vehicle_major_part_number, vehicle_sub_part_number);

This will of course get two rows less than the other options, but maybe you will find a way to prevent the usage of LIKE. This would be much better.

Here a fiddle according to your example: db<>fiddle

Use * Like operator to search all columns of table in sql query

No, it's not possible.

Try it here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in with following request:

SELECT * FROM Customers WHERE * LIKE "%an";

You'll get:

Syntax error (missing operator) in query expression '* LIKE "%an"'.

---

Related Topics