SQL- Between clause for text data type

[cookiesmack101]

Hey, I don’t understand why the resulted table only shows names up to ‘J’, with ‘J’ not being included. It only works if the name only consist of the letter ‘J’.

SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';

I’m aware that you could write the same code as following:

SELECT *
FROM movies
WHERE name >= 'A' AND name <= 'J';

Yet that makes me even more confused. Can someone explain why ‘J’ is not included?

[lisalisaj]

Because it looks at the entire string and not just “J”, so you’d only get movies that start with A-I, or just the letter ‘J’. It wouldn’t get you a result of “Jaws”.

You could use:

substr(name,1,1) BETWEEN 'A' and 'J'

Or you could use:

WHERE name >= 'A' AND name <= 'I'