CODEWITHSUNDEEP
sqloracle-databasebetween
Dominik Ludwin
Dominik Ludwin

Reputation: 19

Oracle SQL BETWEEN not returning last value

I'm using Oracle SQL Developer and after executing this command only names starting with K and L show up. Why names starting with M do not appear?

SELECT DISTINCT(names) FROM STUDENTS WHERE names BETWEEN 'K%' AND 'M%' ORDER BY 1 DESC;

And when I execute:

SELECT DISTINCT(names) FROM STUDENTS WHERE names BETWEEN 'K%' AND 'N%' ORDER BY 1 DESC;

K,L,M appear but names starting with N don't. BETWEEN is inclusive so what's the problem?

This works perfectly:

SELECT * FROM STUDENTS WHERE year BETWEEN 1 AND 3;

Upvotes: 0

Views: 317

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 220877

The between predicate does not imply like so you're simply comparing strings. Write this instead

-- Names with starting letters K, L, M, N
names >= 'K' AND names < 'O'

Alternatively, use regular expressions

-- Names with starting letters K, L, M
REGEXP_LIKE (names, '^[K-M]')

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Lukas explained the problem -- confusing LIKE patterns with strings. One other solution is:

WHERE SUBSTR(names, 1, 1) BETWEEN 'K' AND 'M'

Note that of the solutions mentioned, only the direct comparison:

WHERE names >= 'K' AND names < 'O'

will use an index. However, with such a broad range of values, an index may not be useful for the query anyway.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

BETWEEN is inclusive so what's the problem?

The wildcard '%' does not work as with LIKE here and lexicographic order applies

SELECT CASE WHEN 'Mango' BETWEEN 'K%' AND 'M%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- false

SELECT CASE WHEN 'Mango' >= 'K%' AND 'Mango' <= 'M%' THEN 'true' ELSE 'false' END 
       AS result
FROM dual
--false

SELECT CASE WHEN 'Mango' >= 'K%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- true

SELECT CASE WHEN 'Mango' <= 'M%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- false

db<>fiddle demo

This works perfectly:

SELECT * FROM STUDENTS WHERE year BETWEEN 1 AND 3;

Rewriting in similar manner(though it is not SARGable):

SELECT DISTINCT(names) 
FROM STUDENTS 
WHERE SUBSTR(names,1,1) BETWEEN 'K' AND 'M' 
ORDER BY 1 DESC;

Upvotes: 1

Related Questions