How to check if string contains just lowercase characters and numbers in SQL

Question

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

i believe lower(R1) migh help. something like this: https://stackoverflow.com/questions/14962419/is-the-like-operator-case-sensitive-with-sql-server — ezop, May 26 '22 at 21:17

score 1 · Accepted Answer · answered May 26 '22 at 21:12

1

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

answered May 26 '22 at 21:12

Tim Biegeleisen

502,043
27
286
360

Thanx but that does not works with me, idk why but DB2 cant find the function REGEXP_LIKE can you please rewrite it but with the function LIKE – ahm5 May 26 '22 at 22:05

score 0 · Answer 2 · answered May 27 '22 at 09:18

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)

S	TRANSLATED	RES
abc	>* <	NO
abc@123	> <	YES
Abc	>A <	NO

How to check if string contains just lowercase characters and numbers in SQL

2 Answers2