Sql server wild search on multiple words with 'IN' clause and not LIKE clause!

And if you want to put those with more matches at the top:

SELECT *
FROM [Table A]
WHERE Column2 LIKE '%steam%'
OR Column2 LIKE '%trap%'
ORDER BY
    CASE WHEN Column2 LIKE '%steam%' THEN 1 ELSE 0 END +
    CASE WHEN Column2 LIKE '%trap%' THEN 1 ELSE 0 END DESC

declare @SearchIn nvarchar(1000)
set @SearchIn ='steam, trap'

select * from yourTable
where charindex(rtrim(Column2),@SearchIn,1) > 0

sm394 wrote:

declare @SearchIn nvarchar(1000)
set @SearchIn ='steam, trap'

select * from yourTable
where charindex(rtrim(Column2),@SearchIn,1) > 0
--------------------------------------------------------------------------------------------------------

This won't work. As it will look for exact match i.e 'steam, trap' as one word.

ragbn wrote:

Select * from Table A where Column2 like '%steam%' OR  Column2 like '%trap%'

This should work for you
------------------------------------------------------------------------------------------------

This will work but as I said I will have to dynamically create where clause. Since I won't know what
end-users will try to do search on. It could be 'A B' or 'A B C' or 'A B C D'.

And for right now this seems to be the only solution so far.

I wish there was something like:
    Select *
      from tableA
   where column1
contains any words like(%'steam%trap%abc%efg%')

i.e steam, trap, abc or efg.

ScottPletcher wrote:

And if you want to put those with more matches at the top:

SELECT *
FROM [Table A]
WHERE Column2 LIKE '%steam%'
OR Column2 LIKE '%trap%'
ORDER BY
    CASE WHEN Column2 LIKE '%steam%' THEN 1 ELSE 0 END +
    CASE WHEN Column2 LIKE '%trap%' THEN 1 ELSE 0 END DESC
------------------------------------------------------------------------------------------------
Thanx. Your this solution did help me to solve my other issue with order by. This will come handy.

ScottPletcher wrote:

You could also look into Fulltext indexing.
------------------------------------------------------------------------------------------------

I think this might work. I'm looking into it right now.

ScottPletcher wrote:

You could also look into Fulltext indexing.
------------------------------------------------------------------------------------------------

THIS DID THE TRICK!!! ---> HATS OFF to ScottPletcher :)

After defining full-text index on tableA - Column1, I can now do search for any number of words in column2. If search includes any special characters it still works. For instance if my search text is
'Steam, Trap'. Full text indexing will consider comma(',') as another word and won't treat 'Steam comma(',') as one word. Sleek!
                                                       
                                                    I HAVE THE POWER OF SQL..
                                                                     HE-MAN
select * from Table A
WHERE FREETEXT (Column2, 'Steam Trap' )

And if records in my table were:

Table A
----------------------
Column1| Column2
----------------------
1            |  steam trap
2            |  steam
3            |  trap
4            |  heat trap
5            |  steam engine
6            |  xyz

Resultset will bring back records 1,2,3,4,5.

All points goes to ScottPletcher. Thank you.

Good going!