search for key words in multiple columns

Question

I have 30 columns in my table and I want to select data from the table based on 15 key words that could be found in any of the 30 columns. How do I do write a better query

This is my sample code, I am interested in key words like %Request%, %Attempt% to name a few.

CREATE TABLE #test(
[Date] [datetime] NULL,
[AZ102] nvarchar NULL,
[AL501] nvarchar NULL,
[TX092] nvarchar NULL,
[MN111] nvarchar NULL,
[NY999] nvarchar NULL
) ON [PRIMARY]
GO

INSERT INTO #test
SELECT '1/1/2000', 'Passed test', 'Failed test', 'did not attempt', 'Passed test', 'Passed test' UNION ALL
SELECT '1/1/2001', 'Not included in test', 'Passed test', 'Failed Test', 'Passed Test', 'Passed test' UNION ALL
SELECT '1/1/2002', 'Failed Test', 'Request denied', 'Passed test', 'Passed Test', 'Did not request' UNION ALL
SELECT '1/1/2003', 'Not included in test', 'Passed test', 'Failed test', 'Passed Test', 'Passed test' UNION ALL
SELECT '1/1/2004', 'Requested Test', 'Passed test', 'Not included in test', 'Passed Test', 'Failed test' UNION ALL
SELECT '1/1/2005', 'Not included in test', 'Attempted', 'Passed test', 'Did not Attempted', 'Not included in test'

Answer 1

Hi @ojmp2001ojmp2001-0652m,

Welcome to the microsoft TSQL Q&A forum!

SQL server is a relational database management system,your needs are not suitable for using sql server to solve. As other experts have said, I also think that excel can better solve your problem, and the find function in excel can easily help you solve the problem.

If you really want to use sql to solve it, then for your 15 keywords, you need to write 15 queries similar to the following:

    CREATE TABLE #test(      
    [Date] [datetime] NULL,      
    [AZ102] [nvarchar](255) NULL,      
    [AL501] [nvarchar](255) NULL,      
    [TX092] [nvarchar](255) NULL,      
    [MN111] [nvarchar](255) NULL,      
    [NY999] [nvarchar](255) NULL      
    ) ON [PRIMARY]      
    GO      
    INSERT INTO #test      
    SELECT '1/1/2000', 'Passed test', 'Failed test', 'did not attempt', 'Passed test', 'Passed test' UNION ALL      
    SELECT '1/1/2001', 'Not included in test', 'Passed test', 'Failed Test', 'Passed Test', 'Passed test' UNION ALL      
    SELECT '1/1/2002', 'Failed Test', 'Request denied', 'Passed test', 'Passed Test', 'Did not request' UNION ALL      
    SELECT '1/1/2003', 'Not included in test', 'Passed test', 'Failed test', 'Passed Test', 'Passed test' UNION ALL      
    SELECT '1/1/2004', 'Requested Test', 'Passed test', 'Not included in test', 'Passed Test', 'Failed test' UNION ALL      
    SELECT '1/1/2005', 'Not included in test', 'Attempted', 'Passed test', 'Did not Attempted', 'Not included in test'      
          
    SELECT * FROM #test      
    WHERE [AZ102] LIKE '%Request%' OR [AL501] LIKE '%Request%'       
    OR [TX092] LIKE '%Request%' OR [MN111] LIKE '%Request%'       
    OR [NY999] LIKE '%Request%'      

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Good day @ojmp2001 ojmp2001

How do I do that without writing the where clause without repeating the columns 15 times for each column?

What is more important to you: (1) Write a shorter query, or (2) Use a better query which returns result faster and use less resources?!?

Do you see where I'm pointing you to?

Your request make no sense (at least not without more information which excuses why this request is relevant). The query is something that we write once and our goal should not be "how can I become more lazy" but "how to provide better solution" (better solution usually means better performance

Answer 3

Adding to Ronen's answer, I like to add that the reason you end up here is likely to be design error. In a relational database a table is supposed to model a unique entity, and a column is supposed to model a unique attribute of that entity.

With that model, your request does not make much sense.

Probably all these columns should have been rows in a table - that would make the query a lot easier to write.

As it stands, you will need to write - or as Ronen suggests, generate - a query with WHERE col1 LIKE @this OR col2 LIKE @this ...

Answer 4

This won't be very efficient - but essentially what you need to do is either generate a query with all of the possible OR'd conditions, or unpivot the data to perform the search. This method unpivots the data and then filters:

Select t.[Date]
      , t.AL501
      , t.AZ102
      , t.MN111
      , t.NY999
      , t.TX092
      , u1.MatchColumn
      , u1.MatchValue
   From #test                   t
  Cross Apply (Values (t.[Date], t.AL501, 'AL501')
                    , (t.[Date], t.AZ102, 'AZ102')
                    , (t.[Date], t.MN111, 'MN111')
                    , (t.[Date], t.NY999, 'NY999')
                    , (t.[Date], t.TX092, 'TX092')
              ) As u1(MatchDate, MatchValue, MatchColumn)

  Where u1.MatchValue Like '%Request%'
     Or u1.MatchValue Like '%Attempt%'
     Or u1.MatchValue Like '%Failed%';

The above returns the rows that match - but will include multiple rows if multiple columns match. For example - if you also search for %Failed% then you would get 2 rows for date 2001-01-01, 3 rows for 2002-01-01, etc...

I would probably just return the date and the matching value plus the source column that matched:

 Select u1.MatchDate
      , u1.MatchValue
      , u1.MatchColumn
   From #test                   t
  Cross Apply (Values (t.[Date], t.AL501, 'AL501')
                    , (t.[Date], t.AZ102, 'AZ102')
                    , (t.[Date], t.MN111, 'MN111')
                    , (t.[Date], t.NY999, 'NY999')
                    , (t.[Date], t.TX092, 'TX092')
              ) As u1(MatchDate, MatchValue, MatchColumn)

  Where u1.MatchValue Like '%Request%'
     Or u1.MatchValue Like '%Attempt%'
     Or u1.MatchValue Like '%Failed%';  

If you need to return a single row and all columns - then do not include the 'matching' columns in the select and use DISTINCT to eliminate the duplicate rows, but be aware that doing so will impact the performance of the query.