Search code examples
sqlsql-servercontainssql-like

SQL Server - Contains Query

Using SQL Server 2014 and the following data:

ID          Address         City            State
1           55 Main St      Dallas          TX
2           4 Elm Blvd      Cupertino       CA
3           66 Walnut       Miami           FL
4           21 Main Ave     Cupertino       CA

I'm trying to use a contains query across multiple columns to find matches, but can't figure out the proper syntax. In this case, I have the query part:

CONTAINS ((Address, City, State), '"main" or "cupertino")

This returns rows #1, #2, & #4.

I can also try this:

CONTAINS ((Address, City, State), '"main" and "cupertino")

This doesn't return any rows.

What I'm trying to figure out though, is how I would return just row #4 using the search terms "main" and "cupertino" using a contains query.

So basically, I'm trying to do the following but with a contains query:

WHERE (Address LIKE '%main%' OR City LIKE '%main%' OR Zip LIKE '%main%') AND (Address LIKE '%cupertino%' OR City LIKE '%cupertino%' OR Zip LIKE '%cupertino%')

Thanks!

Solution

  • The expression is for CONTAINS() is computed on each each column independently (as you might have guessed). One solution is to try:

    CONTAINS((Address, City, State), '"main"') AND
CONTAINS((Address, City, State), '"cupertino"')

    The more conventional method is to add a computed column and use that for the indexing:

    alter table t add acs as (Address + ' ' + City + ' ' + State) persisted;

    Then build the index on that column.