How to check if the string contains just lowercase characters, numbers and the symbol "@" in SQL

Im trying something like this:

ALTER TABLE TABLE1
    ADD CONSTRAINT c1
        CHECK (
            (R1 like '%[abcdefghijklmnobqrstuvwxyz@1234567890]%')
            );

CodePudding user response：

You should phrase the check constraint as the column not having any characters which are not lowercase, numbers, or the @ symbol:

ALTER TABLE TABLE1
ADD CONSTRAINT c1
    CHECK (NOT REGEXP_LIKE(R1, '[^abcdefghijklmnobqrstuvwxyz@1234567890]'));

CodePudding user response：

Try this:

SELECT 
  S
, '>' || TRANSLATE (S, '*', ' abcdefghijklmnobqrstuvwxyz@1234567890') || '<' AS TRANSLATED
, CASE TRANSLATE (S, '*', ' abcdefghijklmnobqrstuvwxyz@1234567890') 
    WHEN '' THEN 'YES'
    ELSE 'NO'
  END AS RES
FROM (VALUES ' abc', 'abc@123', 'Abc') T (S)