DEV Community

Cover image for Like, so confused...
Inee Ader 🐌
Inee Ader 🐌

Posted on

Like, so confused...

LIKE operator

I'm taking a SQL Essential Training course on LinkedIn Learning, and I BRIEFLY got confused about the like operator.

I kept forgetting what was what in terms of where the % went.

I'm using SQLStudio and following this tutorial.

Here are my notes:

%[search_term]% - CONTAINS

SELECT Name, Continent FROM Country WHERE Name LIKE '%island%';
Enter fullscreen mode Exit fullscreen mode

Human sentence: "Show the Name and Continent column of the Country table that CONTAINS 'island'..."

% on either side envelopes the key search word, so 'contain' here makes sense.
Screen Shot 2021-04-05 at 1.05.31 PM
results that **contain* 'island' in any way*

%[search_term] - ENDS WITH

SELECT Name, Continent FROM Country WHERE Name LIKE '%island';
Enter fullscreen mode Exit fullscreen mode

Human sentence: "Show me the Name and Continent column of the Country table that ENDS with 'island'..."

% only at the beginning of the search term takes the place of the rest of the text, meaning that % at the beginning means 'island' at the end.
Screen Shot 2021-04-05 at 1.10.15 PM
only results that **end* with 'island'*

[search_term]% - BEGINS WITH

SELECT Name, Continent FROM Country WHERE Name LIKE 'island%';
Enter fullscreen mode Exit fullscreen mode

Human sentence: "Show me the Name and Continent column of the Country table that STARTS with 'island'..."

% only at the end of the search term means 'island' is at the beginning of the Name column.
Screen Shot 2021-04-05 at 1.11.27 PM
we get a big whopping zilch for this one...I guess there's no Country Name that BEGINS with the word 'island'.

But let's add one for fun to make sure it works...
Screen Shot 2021-04-05 at 1.13.54 PM
Ok so now we have the fictional 'Island of the Blue Dolphins' which was now returned

For some reason it's hard for me to remember which term it searches for based on where the % is located.

Hope this helps, but mostly this is for me :)

Latest comments (2)

Collapse
 
darkain profile image
Vincent Milum Jr

A better way to think of the % character in LIKE queries: it is a "find and replace" character. Meaning, anywhere that % is found, it can be replaced by ANYTHING and still match. This also means that it works in the middle, too! So you can have "A%C" and it'll match on "ABC" and "AXC" and "A bob is really awesome C" for example.

Collapse
 
ineeader profile image
Inee Ader 🐌

THANK YOU! I hadn't gotten as advanced as those other % advanced options yet! I appreciate the clarification.