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.

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. — RBarryYoung, Aug 30 '13 at 13:38

score 10 · Accepted Answer · edited May 23 '17 at 12:22

10

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

edited May 23 '17 at 12:22

Community

1
1

answered Aug 30 '13 at 12:38

Roman Pekar

107,110
28
195
197

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]` – Devart Aug 30 '13 at 13:20
@Devart yes it's possible too, don't think it would be much different in performance – Roman Pekar Aug 30 '13 at 13:21
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. – Devart Aug 30 '13 at 13:23
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. – Roman Pekar Aug 30 '13 at 13:32
Thanks and you for the reply. PS: Run queries on `SQL Server 2012` with cold start (selected only column_name). – Devart Aug 30 '13 at 13:39
@Devart 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. – RBarryYoung Aug 30 '13 at 13:43
4

In addition, it should be understood that contains function is case-sensitive and uses xQuery default Unicode code point collation for the string comparison http://sqlfiddle.com/#!3/473e5/5 BTW +1 for a cool answer;) – Aleksandr Fedorenko Aug 30 '13 at 16:10
1

@AlexanderFedorenko that's a very good point! thanks for mentioning that – Roman Pekar Aug 30 '13 at 16:16
@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' – bernie2436 Aug 30 '13 at 17:47
1

If you mean second one, you have to set `@search = '%en%'`, like here http://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. – Roman Pekar Aug 30 '13 at 18:07

score 1 · Answer 2 · answered Aug 30 '13 at 17:42

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.

this could be subject of sql injection - that's why I've used sp_executesql with parameters — Roman Pekar, Aug 30 '13 at 21:01

score 0 · Answer 3 · answered Apr 11 '14 at 14:20

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'.

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

3 Answers3