Search all columns with the same where clause

Question

Is there a way to search all columns with the same where clause? It is one piece of text and ideally not write a long where with or statement would be nice.

Answer 1

Hi @Jonathan Brotto,

Please try the following solution based on SQL Server's XML and XQuery functionality.

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, City VARCHAR(20), State CHAR(2), Zip VARCHAR(20));
INSERT @tbl (City, State, Zip) VALUES
('Miami', 'FL', '33160'),
('Dallas', 'TX', '15098'),
('Los Angeles', 'CA', '45660');
-- DDL and sample data population, end
 
DECLARE @par_Search VARCHAR(20) = 'ami';
 
-- Method #1
-- exact search, case insensitive
SELECT t.* --, x
FROM @tbl AS t
       CROSS APPLY (SELECT t.* FOR XML PATH('r'), TYPE) AS t1(x)
WHERE x.exist('/r/*[lower-case(text()[1]) = lower-case(sql:variable("@par_Search"))]') = 1;
 
-- Method #2
-- partial comparison, case insensitive
SELECT t.* , x
FROM @tbl AS t
       CROSS APPLY (SELECT t.* FOR XML PATH('r'), TYPE) AS t1(x)
WHERE x.exist('/r/*[contains(lower-case(text()[1]), lower-case(sql:variable("@par_Search")))]') = 1;

Answer 2

Try (replacing my table name and schema with your table name and schema):

DECLARE @allStringColumns VARCHAR(MAX)
DECLARE @sql nVARCHAR(MAX)
SELECT @allStringColumns = STRING_AGG(col.COLUMN_NAME, ' = @StringToSearch OR ')
FROM INFORMATION_SCHEMA.COLUMNS col
WHERE col.TABLE_NAME = 'prov_pmf_indiv_provider'
AND col.DATA_TYPE LIKE '%char'
--SET @allStringColumns = LEFT(@allStringColumns, LEN(@allStringColumns) - 4)
SET @sql = 'select top(10) * from consume_tbl.prov_pmf_indiv_provider ' + 
'where ' + @allStringColumns + '= @StringToSearch'
PRINT @sql
EXECUTE sys.sp_executeSQL @sql, N'@StringToSearch varchar(max)', @StringToSearch = 'Test';