SQLTeam.com | Weblogs | Forums

CONTAINS syntax

Hello

Can you help me please with the CONTAINS syntax as it seems there are some MS Access documentation that is confused with SQL.

I want to select values in Col1 that match:
%a% or %b%

I would like to use CONTAINS.

Any hint?

Thanks!

you can use LIKE operator in SQL server.

Thanks but I end up having a large number of:
Col1 like '%a%' or
Col1 like '%b%' etc

What are you trying to solve? Can you provide sample data for us to work with?

It is just to have multiple criteria in a more concise way, e.g.:

Col1
aba
ant
reo
roy
nir
tom
yfi
etc

Instead of doing:
WHERE
Col1 like '%a%' or
Col1 like '%b%' or
etc

I was looking for a way to say:
Col1 contains '%a%' or '%b%' or etc

Maybe

Where col1 like '%[a-z]%'

Good shout but a, b, etc was an example, in reality it will be fgr, tgh, eyb, erv etc :slight_smile:

Read it again and look at the help documwnt on LIKE look for range

1 Like

There are several ways of simplifying your LIKE statements as @yosiasz points out. You can also pre-process the search criteria into a temp table and then join to the temp table.

But - if you are looking for a simpler syntax then you might be better off setting up a text catalog and using CONTAINS.

hi hope this helps

1 Like