How to search multiple values ​​in a column where I need to use wildcards?

Question

In SQL Server, I need to find a column for multiple values, but I don't have exact values, so I also need to use wildcards.

My current request looks like this:

SELECT * FROM table WHERE fieldname in ( '%abc1234%', '%cde456%', '%efg8976%') 

This does not return any results, and yet, if I search for any one value, I find it, so I know that they are there. If you don't do a few ORs that are a bit cumbersome with a few hundred values, is there any way to do this?

I would also be interested to know why this query does not work, since the same query without% works just fine (except for a small problem only for finding a few exact matches).

Answer 1

Look at the full text . This should greatly improve your search and make your "OR" problem a little nicer to download:

 SELECT * FROM table WHERE CONTAINS(fieldname, '"abc1234" OR "cde456" OR "efg8976"') 

See also:

http://www.simple-talk.com/sql/learn-sql-server/full-text-indexing-workbench/

Answer 2

The reason the query doesn't work is because it searches for an exact match for fieldname in the list of values ​​in parens. This does not make a LIKE comparison where wildcards are taken into account.

So your query is equivalent to:

 SELECT * from table where fieldname = '%abc1234%' OR fieldname = '%cde456%' OR fieldname = '%efg8976%' 

Obviously you do not want to.

Answer 3

 select table.* from ( table join ( select * from values ( ('%abc1234%'), ('%cde456%'), ('%efg8976%') ) ) as search( exp ) on 0 = 0 ) where fieldname like exp 

or maybe

 select table.* from table join ( select * from values ( ( '%abc1234%' ), ( '%cde456%' ), ( '%efg8976%' ) ) as search( exp ) on fieldname like exp 

Modulo some syntax I'm sure.

The fact is that this is approaching the fact that the list of values ​​is the only parameter.