SQL, putting together LIKE queries

Question

SELECT name
FROM TV
WHERE name LIKE '%Day%'
AND name NOT LIKE '%Days%'
            AND name NOT LIKE '%Hayday%'
            AND name NOT LIKE '%Payday%'
            AND name NOT LIKE '%Doomsday%'

How could I merge the last 3 queries so that the word 'day' is not included in any word except for a word on its own?

Are you using MySQL or SQLite? – Tim Biegeleisen Sep 26 '18 at 07:46 — Tim Biegeleisen, Sep 26 '18 at 07:46

Guy Louzon · Answer 1 · 2018-09-26T09:13:25.150

3

Spaces are a part of a string... assuming that you're searching inside a phrase, Or at the beginning or end of it. This is a simple case solution, assuming no punctuations, just texts and spaces. then a solution should be something like:

SELECT name
FROM Track
WHERE name (name  LIKE '% Day %') OR (name  LIKE '% Day') OR (name  LIKE 'Day %')
;

edited Sep 26 '18 at 09:13

answered Sep 26 '18 at 07:44

Guy Louzon

1,175
9
19

2

Will miss "Another time." – jarlh Sep 26 '18 at 07:52
How come ? (and anyway, the question was edited to search for "days"...) – Guy Louzon Sep 26 '18 at 07:59
Still won't match "Another Day." – Nick Sep 26 '18 at 08:04
Why ? its either with a space after the y, or no character at all – Guy Louzon Sep 26 '18 at 08:27
1

Who's to say there isn't punctuation as @jarlh pointed out in his original comment? – Nick Sep 26 '18 at 09:05
took me a little bit, but I got it...:) you're right. I'll add a remark to my answer – Guy Louzon Sep 26 '18 at 09:12

Nick · Answer 2 · 2018-09-26T08:06:29.537

3

If you are using MySQL, you can use RLIKE (REGEXP) to match only against the word by itself by creating an expression with word boundaries:

SELECT name
FROM TV
WHERE name REGEXP '[[:<:]]Day[[:>:]]'

Note that REGEXP is case-insenstive so this will also match the word "day".

If you are using SQLite, it is possible you have REGEXP installed in which case this solution should work for you too. If not, this question gives guidance as to how you may be able to install it.

edited Sep 26 '18 at 08:06

answered Sep 26 '18 at 07:50

Nick

138,499
22
57
95

And none of the other answers using `LIKE` can do what your query is doing. Maybe add a comment that `REGEXP` is case insensitive. – Tim Biegeleisen Sep 26 '18 at 07:51
@TimBiegeleisen indeed. Unforuntately SQLite doesn't have REGEXP installed by default. – Nick Sep 26 '18 at 07:53
No...but it might also support `REGEXP`, which still helps your answer even in that case. – Tim Biegeleisen Sep 26 '18 at 07:53

score 0 · Answer 3 · answered Sep 26 '18 at 07:44

0

In your query, instead of 'Like' keyword, you can directly give the string like "where name ='time'

answered Sep 26 '18 at 07:44

karthi keyan

53
2
7

score 0 · Answer 4 · answered Sep 26 '18 at 07:45

0

You can by using FIND_IN_SET

SELECT name
FROM Track
WHERE name LIKE '%Time%'
AND FIND_IN_SET(name,"Times,Playtime,Summertime,Wartime")<0

answered Sep 26 '18 at 07:45

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 5 · answered Sep 26 '18 at 11:14

Assuming by "on its own" you mean surrounded by spaces, then the simplest method might be:

SELECT name
FROM TV
WHERE ' ' || name || ' ' LIKE '% Day %';

(Or however your database does string concatenation.)

If "on its own" simply means a word boundary, then regular expressions are the best method.

SQL, putting together LIKE queries

5 Answers5