How to select from table all names that started on any letter – ORACLE

November 5, 2021

How to select from table all names that contains any letter?
For example if variable is ‘BANANA’ it is great, if variable is ‘*895-+59’ it is not great

IF variable LIKE '%A-Z%' THEN
        dbms_output.put_line('GREAT');

>Solution :

How to select from table all names that contains any letter?

You cannot use regular expressions in a LIKE comparison but you can use them in REGEXP_LIKE:

SELECT name
FROM   table_name
WHERE  REGEXP_LIKE(name, '[A-Za-z]')

If you want the column to start with a letter then anchor it to the start of the string:

SELECT name
FROM   table_name
WHERE  REGEXP_LIKE(name, '^[A-Za-z]')

If you want to output Great or Not Great then put it in a CASE expression rather than a WHERE filter:

SELECT name,
       CASE 
       WHEN REGEXP_LIKE(name, '[A-Za-z]')
       THEN 'Great'
       ELSE 'Not Great'
       END AS is_great
FROM   table_name