how do I select records that are like some string for any column in a table?

Question

I know that I can search for a term in one column in a table in t-sql by using like %termToFind%. And I know I can get all columns in a table with this:

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

How can I perform a like comprparison on each of the columns of a table? I have a very large table so I can't just spell out LIKE for each column.

Touchmenot

If you think that you might be doing things like this a lot in the future, then you should look at the Full-Text Search facility of SQL Server. @RomanPekar's XML approach will work, but it cannot really use indexes, so won't be fast. That's fine if you only do this once in a while, but if you need this for online user queries, your server will get bogged down pretty quickly. – Unlovely

@Unlovely very true – Muricate

Answer 1

As always, I'll suggest xml for this (I'd suggest JSON if SQL Server had native support for it :) ). You can try to use this query, though it could perform not so well on large number of rows:

;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 more detailed example.

Important note by Alexander Fedorenko in comments: it should be understood that contains function is case-sensitive and uses xQuery default Unicode code point collation for the string comparison.

More general way would be to use 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

Muricate

IMHO: sys.columns don't contains hidden joins in comparison with INFORMATION_SCHEMA.COLUMNS: SELECT c.name FROM sys.columns c WHERE OBJECT_ID('dbo.test') = c.[object_id] – Carrick

@Carrick yes it's possible too, don't think it would be much different in performance – Muricate

On very huge db: sys.columns (1 row(s) affected) SQL Server Execution Times: CPU time = 203 ms, elapsed time = 878 ms. INFORMATION_SCHEMA.COLUMNS (1 row(s) affected) SQL Server Execution Times: CPU time = 1794 ms, elapsed time = 1944 ms. – Carrick

you have to select only column names from both view to compare performance of this particular query :) On one of my tables sys.columns outperformed information_schema.columns like 1/3. Actually it's strange, because if joins written properly and select made on only particular columns which not use this joins, optimizer should recognize it. Anyway, actually I've never liked INFORMATION_SCHEMA, my SQL objects scripting using sys.columns. – Muricate

Thanks and you for the reply. PS: Run queries on SQL Server 2012 with cold start (selected only column_name). – Carrick

@Carrick The overhead of sys vs. INFORMATION_SCHEMA is very likely trivial here, as this kind of query require a full table scan of the data table for every execution. Unless the data table is small, that will be the dominant performance factor. – Unlovely

In addition, it should be understood that contains function is case-sensitive and uses xQuery default Unicode code point collation for the string comparison sqlfiddle.com/#!3/473e5/5 BTW +1 for a cool answer;) – Tifanytiff

@AlexanderFedorenko that's a very good point! thanks for mentioning that – Muricate

@RomanPekar nifty answer! But if I set the -@search = 'en' in your sql fiddle demo it does not pull any records. I am looking for something that does a like operation. I would expect both of the Lennon records to match because they both contain 'en' – Touchmenot

If you mean second one, you have to set @search = '%en%', like here sqlfiddle.com/#!3/b9eec/20. You also can change like @search in dynamic SQL to 5like ''%'' + @search + ''%'', but I think that setting % in parameter is more flexible. – Muricate

Answer 2

I'd use dynamic SQL here.

Full credit - this answer was initially posted by another user, and deleted. I think it's a good answer so I'm re-adding 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 benefit of this is that it will utilize any index you have on your table, and there is no overhead in constructing the XML CTE for querying.

Potty

this could be subject of sql injection - that's why I've used sp_executesql with parameters – Muricate

Answer 3

In case someone is looking for PostgreSQL solution:

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

will select all records that have 'your_value' in any column. Didn't try this with any other database.

Unfortunately this works as combining all columns to a text string and then searches for a value in that string, so I don't know a way to make it match "whole cell" only. It will always match if any part of any cell matches 'your_value'.

Basion