Oracle SQL Like

Question

I'm trying to get all the addresses that start with letters, when I say a specific letter there are no problems.

    SELECT customer_name, customer_address 
FROM Customers
WHERE CUSTOMER_ADDRESS LIKE 'A%';

But when I try from A-Z, it does not show any result

SELECT customer_name, customer_address 
FROM Customers
WHERE CUSTOMER_ADDRESS LIKE '[A-Z]%';

Someone knows what i'm doing wrong? (i already try without [], '', %[A-Z]%, [a-z]%)

I'm using Oracle SQL Developer.

Answer 1

Assuming you are using ASCII character and you want to be able to use an index, you can phrase this as:

WHERE CUSTOMER_ADDRESS >= 'A' AND
      CUSTOMER_ADDRESS < '['  -- the ASCII character one bigger than 'Z'

This is a bit arcane, but it does a direct comparison so can use indexes (and even without indexes would be marginally faster).

Answer 2

Valid wildchars for LIKE are % for an unknown number of characters and _ for one character. So LIKE '[A-Z]%' looks for strings starting with the character [, followed by A, followed by -, ...

You want REGEXP_LIKE instead:

SELECT customer_name, customer_address 
FROM Customers
WHERE REGEXP_LIKE(customer_address, '^[A-Z].*');

Answer 3

You could use regex_like

WHERE REGEXP_LIKE (CUSTOMER_ADDRESS , '^[A-Z]');