Search all columns with the same where clause

Jonathan Brotto 200 Reputation points
2024-06-11T18:15:45.0166667+00:00

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.

SQL Server Training
SQL Server Training
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Training: Instruction to develop new skills.
11 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,731 Reputation points
    2024-06-11T20:14:15.99+00:00

    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;
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Naomi Nosonovsky 525 Reputation points
    2024-06-11T18:43:13.9866667+00:00

    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';
    
    
    1 person found this answer helpful.