Oracle BETWEEN operator with text values

Question

Query 1:

SELECT cust_last_name, cust_credit_limit
FROM customers
WHERE (UPPER(cust_last_name) LIKE 'A%' OR
       UPPER(cust_last_name) LIKE 'B%' OR
       UPPER(cust_last_name) LIKE 'C%' AND cust_credit_limit < 10000;

Query 2:

SELECT cust_last_name,cust_credit_limit
FROM customers
WHERE UPPER(cust_last_name) BETWEEN 'A' AND 'C' AND
cust_credit_limit < 10000;

I am trying to generate a report of last names and credit limits of all the customers whose last names start with A, B or C and credit limit is below 10000. Which is the correct way?

Gurwinder Singh · Accepted Answer · 2017-02-27 03:43:09Z

2

You can't directly use between A and C because the values that start with C like CDE will not be included. Since you need all the row starting with A to starting with C, you could use a custom range:

select cust_last_name, cust_credit_limit
from customers
where UPPER(cust_last_name) >= 'A'
  and UPPER(cust_last_name) < 'D'
  and cust_credit_limit < 10000

It finds all strings that are between A (inclusive) and D (exclusive).

Demo

or use maybe substr:

select cust_last_name, cust_credit_limit
from customers
where UPPER(substr(cust_last_name,1,1)) between 'A' and 'C'
    and cust_credit_limit < 10000

Demo

edited Feb 27, 2017 at 3:43

answered Feb 27, 2017 at 3:37

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-02-27 03:47:49Z

0

Oracle is likely to use an index for the following query:

select cust_last_name, cust_credit_limit
from customers
where ((cust_last_name >= 'A' and cust_last_name < 'D') or
       (cust_last_name >= 'a' and cust_last_name < 'd')
      ) and
      cust_credit_limit < 10000;

For performance, I would suggest that you go with the upper() version and create an index on customers(upper(cust_last_name), cust_credit_limit).

answered Feb 27, 2017 at 3:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

BobC Over a year ago

How do you know that these indexes will be beneficial, without knowing the cardinalities?

Gordon Linoff Over a year ago

@BobC . . . I realized that after answering. With the given ranges, these might not be sufficiently selective.

Collectives™ on Stack Overflow

Oracle BETWEEN operator with text values

Query 1:

Query 2:

2 Answers 2

Demo

Demo

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Query 1:

Query 2:

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related