How to select records similar to any row for any column of the table?

Question

I know that I can find the term in one column in a table in t-sql using like %termToFind% . And I know that I can get all the columns of the table:

 SELECT * FROM MyDataBaseName.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'MyTableName` 

How can I do a similar match on each column of the table? I have a very large table, so I cannot just specify LIKE for each column.

Answer 1

As always, I suggest xml for this (I would suggest JSON if SQL Server had built-in support :) :). You can try using this query, although it may not work as well with a large number of lines:

 ;with cte as ( select *, (select t.* for xml raw('data'), type) as data from test as t ) select * from cte where data.exist('data/@*[local-name() != "id" and contains(., sql:variable("@search"))]') = 1 

see sql fiddle demo for a more detailed example.

It is important to note Alexander Fedorenko in the comments: it should be understood that the contains function is case sensitive and uses xQuery's default Unicode code point to compare strings.

A more general way is to use a dynamic SQL solution:

 declare @search nvarchar(max) declare @stmt nvarchar(max) select @stmt = isnull(@stmt + ' or ', '') + quotename(name) + ' like @search' from sys.columns as c where c.[object_id] = object_id('dbo.test') -- -- also possible -- -- select @stmt = isnull(@stmt + ' or ', '') + quotename(column_name) + ' like @search' -- from INFORMATION_SCHEMA.COLUMNS -- where TABLE_NAME = 'test' select @stmt = 'select * from test where ' + @stmt exec sp_executesql @stmt = @stmt, @params = N'@search nvarchar(max)', @search = @search 

sql fiddle demo

Answer 2

I would use dynamic SQL here.

Full credit - this answer was originally sent by another user and deleted. I think this is a good answer, so I re-add it.

 DECLARE @sql NVARCHAR(MAX); DECLARE @table NVARCHAR(50); DECLARE @term NVARCHAR(50); SET @term = '%term to find%'; SET @table = 'TableName'; SET @sql = 'SELECT * FROM ' + @table + ' WHERE ' SELECT @sql = @sql + COALESCE('CAST('+ column_name + ' as NVARCHAR(MAX)) like N''' + @term + ''' OR ', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = @table SET @sql = @sql + ' 1 = 0' SELECT @sql EXEC sp_executesql @sql 

The XML answer is cleaner (I prefer dynamic SQL only when necessary), but the advantage of this is that it will use whatever index you have in your table, and there is no overhead when building XML CTE for queries .

Answer 3

If someone is looking for a PostgreSQL solution:

  SELECT * FROM table_name WHERE position('your_value' IN (table_name.*)::text)>0 

will select all records having "your_value" in any column. Did not try this with any other database.

Unfortunately, this works like concatenating all columns into a text row, and then searching for the value in that row, so I don’t know how to make it match only with the whole cell. It will always match if any part of any cell matches "your_value".