Oracle SQL - REGEXP_LIKE contains characters other than string 'NA'

Question

I would like to create a query where I select all records which contain characters that are a-z or A-Z,not allowed string 'NA'

select *
  from tz_customers c 
 where REGEXP_LIKE(c.customercode,'^NA');

REGEXP_LIKE allow all characters other STRING 'NA'

Answer 1

I might use an inequality comparison to exclude NA:

SELECT *
FROM tz_customers
WHERE
    REGEXP_LIKE(customercode, '^[A-Za-z]+$') AND
    customercode <> 'NA';

In principle we could try to use a negative lookahead in a single regex pattern to exclude NA, but I'm not sure if Oracle regex supports that.

Answer 2

If you want strings that do not start witn NA then:

SELECT *
FROM   tz_customers c
WHERE  c.customercode NOT LIKE 'NA%'

If you want strings that don't contain NA anywhere then:

SELECT *
FROM   tz_customers c
WHERE  c.customercode NOT LIKE '%NA%'

If you want strings that aren't exactly NA then:

SELECT *
FROM   tz_customers c
WHERE  c.customercode <> 'NA'

If you want strings that only have alphabet characters then you can use TRANSLATE to strip the allowed characters and test whether the remaining string is empty (NULL):

SELECT *
FROM   tz_customers c
WHERE  TRANSLATE(
         c.customercode,
         '1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
         '1'
       ) IS NULL

If you want to combine the two then:

SELECT *
FROM   tz_customers c
WHERE  TRANSLATE(
         c.customercode,
         '1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
         '1'
       ) IS NULL
AND    c.customercode <> 'NA'

So for the sample data:

CREATE TABLE tz_customers ( customercode ) AS
  SELECT 'NA'      FROM DUAL UNION ALL
  SELECT 'BANANA'  FROM DUAL UNION ALL
  SELECT 'BANANA2' FROM DUAL;

This outputs:

| CUSTOMERCODE |
| :----------- |
| BANANA       |

db<>fiddle here

Answer 3

I don't think you need regexp_like:

  select * from tz_customers where customer_code not like '%NA%'