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

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');

CodePudding user response：

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

CodePudding user response：

You can use REGEXP_SUBSTR() function with conditional such as

SELECT DECODE(SIGN(LENGTH(REGEXP_SUBSTR('<your_expression>','[a-zA-Z]'))),1,'Great','Bad')
  FROM your_table

Demo