SQL: How to search for a substring except in cases where that substring is a part of a specific word-CodePudding

Say that there are rows which contain the following texts 'med', 'medical', 'med medical'.

I want to retrieve all rows which contain the substring 'med' except if that substring is part of the string 'abc medical def'.

My desired result is to only retrieve 'med' and 'med medical' because they contain the substring 'med' and that substring is not a part of 'medical'.

I can use LIKE 'med%' but that would retrieve all 3.

CodePudding user response：

You can simply add another condition:

SELECT * FROM table WHERE field LIKE 'med%' AND field <> 'medical';

CodePudding user response：

If you use Like 'med%' that will return data where field starts with med. It will exclude other data. So you can use :

SELECT * FROM table WHERE field LIKE 'med%' AND field != 'medical'