Selecting Records in SQL using LIKE function to very specific avail-CodePudding

I haven't been able to find a question that addressed an instance as specific as this, but apologies if I've missed something.

If I want to select records that contain a specific word 'Apple', but I want to ensure records that might contain the word 'PineApple' are omitted, would the LIKE operator allow for this in this instance, or would there need to be further conditioning:

e.g

SELECT ID, Manufacturer FROM Table1 WHERE Manufacturer LIKE '%apple%'

Many thanks.

CodePudding user response：

To do this using only LIKE and without regular expressions, you may try:

SELECT ID, Manufacturer
FROM Table1
WHERE Manufacturer LIKE 'apple %' OR    -- at the start of the name
      Manufacturer LIKE '% apple %' OR  -- in the middle
      Manufacturer LIKE '% apple';      -- at the end

Assuming your version of SQL does support some kind of regex like, you could match on the pattern \bapple\b, which means match apple as a standalone word.

CodePudding user response：

If you add a space before and after the value, then you can search by LIKE '% apple %'

SELECT ID, Manufacturer 
FROM Table1 
WHERE LOWER(CONCAT(' ', Manufacturer, ' ')) LIKE '% apple %';