Get the column names matching a multiple column search

Question

For an application, I need to show search results based upon multiple columns. Searching in multiple columns is not that hard, but I also want to know which column(s) the search query matches the text.

Imagine following 'Blabla' table:

---------------------------------------------------------------------------------------
| column1                |  column2                  | column 3                       |
---------------------------------------------------------------------------------------
| Animals are beautiful  | Some text regarding music | Vague statement about politics |
---------------------------------------------------------------------------------------
| Music is not his thing | Green is a color          | Random gibberish about music   |
---------------------------------------------------------------------------------------

Searching for the word 'music' would be as simple as:

SELECT * FROM Blabla WHERE column1 LIKE '%music%' OR column2 LIKE '%music%' OR column2 LIKE '%music%';

But is there a way in SQL to show that matching columns would be column2 for row 1 and column1 + column3 for row 2?

score 1 · Accepted Answer · answered Nov 07 '16 at 09:24

1

You could use a CASE statement:

SELECT CASE WHEN column1 LIKE '%music%' THEN 'Column1' ELSE '' END AS col1match,
       CASE WHEN column2 LIKE '%music%' THEN 'Column2' ELSE '' END AS col2match,
       CASE WHEN column3 LIKE '%music%' THEN 'Column3' ELSE '' END AS col3match
FROM Blabla

answered Nov 07 '16 at 09:24

Tim Biegeleisen

502,043
27
286
360

Accepted your answer as you were the first. Thx for your fast responses guys (also @Anton Gogolev and @sagi). Now I think about it, don't know why I didn't think about this... – Jacob van Lingen Nov 07 '16 at 09:36

score 1 · Answer 2 · answered Nov 07 '16 at 09:25

1

That would be

select
  column1,
  column2, 
  column3,
  case
    when column1 like '%music%' then 'column1'
    when column2 like '%music%' then 'column2'
    when column3 like '%music%' then 'column3'
    else ''
  end as [column]
from Blabla where column1 like '%music%' or column2 like '%music%' or column3 like '%music%'

Not sure about how performant this actually is.

answered Nov 07 '16 at 09:25

Anton Gogolev

113,561
39
200
288

Isn't that what he wants? @TimBiegeleisen – sagi Nov 07 '16 at 09:31

score 1 · Answer 3 · answered Nov 07 '16 at 09:26

Yes, you can use CASE EXPRESSION for indication columns :

SELECT s.* FROM (
    SELECT t.*,
           CASE WHEN t.column1 LIKE '%music%' THEN 1 ELSE 0 as col_1_ind,
           CASE WHEN t.column2 LIKE '%music%' THEN 1 ELSE 0 as col_2_ind,
           CASE WHEN t.column3 LIKE '%music%' THEN 1 ELSE 0 as col_3_ind
    FROM Blabla t) s
WHERE 1 IN(s.col_1_ind,s.col_2_ind,s.col_3_ind)

score 0 · Answer 4 · answered Nov 07 '16 at 09:38

try this,

declare @t table(column1 varchar(100), column2 varchar(100),column3 varchar(100))
insert into @t values
('Animals are beautiful','Some text regarding music','Vague statement about politics')

,('Music is not his thing','Green is a color','Random gibberish about music')

;With CTE as
(
 select column1,column2,column3,ROW_NUMBER()over(order by (select null))rn from @t
)
select case when column1 LIKE '%music%' then 'column1 in row'+ cast(rn as varchar) end
,case when column2 LIKE '%music%' then 'column2 in row'+ cast(rn as varchar) end
,case when column3 LIKE '%music%' then 'column3 in row'+ cast(rn as varchar)
end
from cte

Get the column names matching a multiple column search

4 Answers4

Linked