CODEWITHSUNDEEP
sql
Euclid
Euclid

Reputation: 910

Is it possible to use LIKE and IN for a WHERE statment?

I have a list of place names and would like to match them to records in a sql database the problem is the properties have reference numbers after there name. eg. 'Ballymena P-4sdf5g' Is it possible to use IN and LIKE to match records

WHERE dbo.[Places].[Name] IN LIKE('Ballymena%','Banger%')

Upvotes: 12

Views: 3771

Answers (4)

onedaywhen
onedaywhen

Reputation: 57023

It's a common misconception that for the construct 

b IN (x, y, z)

that (x, y, z) represents a set. It does not.

Rather, it is merely syntactic sugar for

(b = x OR b = y OR b = z)

SQL has but one data structure: the table. If you want to query search text values as a set then put them into a table. Then you can JOIN your search text table to your Places table using LIKE in the JOIN condition e.g. 

WITH Places (Name)
     AS
     (
      SELECT Name
        FROM (
              VALUES ('Ballymeade Country Club'), 
                     ('Ballymena Candles'), 
                     ('Bangers & Mash Cafe'), 
                     ('Bangebis')
             ) AS Places (Name)
     ), 
     SearchText (search_text)
     AS
     (
      SELECT search_text
        FROM (
              VALUES ('Ballymena'), 
                     ('Banger')
             ) AS SearchText (search_text)
     )
SELECT * 
  FROM Places AS P1
       LEFT OUTER JOIN SearchText AS S1
          ON P1.Name LIKE S1.search_text + '%';

Upvotes: 11

Breezer
Breezer

Reputation: 10490

well a simple solution would be using regular expression not sure how it's done in sql but probably something similiar to this

WHERE dbo.[Places].[Name]  SIMILAR TO '(Banger|Ballymena)';

or

WHERE dbo.[Places].[Name] REGEXP_LIKE(dbo.[Places].[Name],'(Banger|Ballymena)');

one of them should atleast work

Upvotes: 2

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

you could use OR

WHERE 
  dbo.[Places].[Name] LIKE 'Ballymena%' 
  OR dbo.[Places].[Name] LIKE 'Banger%'

or split the string at the space, if the places.name is always in the same format.

WHERE SUBSTRING(dbo.[Places].[Name], 1, CHARINDEX(dbo.[Places].[Name], ' ')) 
  IN ('Ballymena', 'Banger')

This might decrease performance, because the database may be able to use indexes with like (if the wildcard is at the end you have even a better chance) but most probably not when using substring.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838036

No, but you can use OR instead:

WHERE (dbo.[Places].[Name] LIKE 'Ballymena%' OR
       dbo.[Places].[Name] LIKE 'Banger%')

Upvotes: 13

Related Questions