-2

How can I write check constraint for one column say class_id where check condition should be: string started with CLS and followed by any 5 digit number. for e.g: CL100987, CLS45678

D. Schreier
  • 1,700
  • 1
  • 22
  • 34
Sakshi Ray
  • 7
  • 3
  • 2
    Which dbms are you using? – jarlh Nov 02 '20 at 08:21
  • 2
    Do you really mean CL100987 is a valid "_string started with "CLS"and followed by any 5 digit number_" value? – jarlh Nov 02 '20 at 08:21
  • RDBMS domain integrity constraint – Sakshi Ray Nov 02 '20 at 08:21
  • yes anything afterwards CL or CLS – Sakshi Ray Nov 02 '20 at 08:22
  • Different databases have different string functions etc. Which dbms are you using? – jarlh Nov 02 '20 at 08:22
  • i didnt understand your question i just wanted a simple check condition for above problem no matter which dbms i am using. – Sakshi Ray Nov 02 '20 at 08:25
  • 1
    For example how to find "5 digit number" will be different depending on the dbms used. – jarlh Nov 02 '20 at 08:29
  • @SakshiRay: please tag your question with the database you are running: mysql, oracle, sqlserver...? String functions differ between databases (as do check constraints capabilities), so there is no answer to your question that would work "no matter which dbms" you are using. – GMB Nov 02 '20 at 08:57

1 Answers1

0

try using regex

something like that

WHERE REGEXP_LIKE (COLUMN_CONDITION,'^CLS.*([0-9]{5})');

| REGEXP_LIKE -> like specific to regex | column_condition -> explicit | '^**." -> start with and followed by any char | ([0-9]) - > only numerics | {5} -> the number of numerics in the expression

pay attention to your dbms, some differences 

hope this could help
manuel antunes
  • 26
  • 6
  • But it also [allows](https://regex101.com/r/EqQHo3/1) `CLS 12345`. And it does not accept `CL100987`, which was in the question. But it really hard for `CL100987` to be the "*string started with "CLS"and followed by any 5 digit number*" – astentx Nov 02 '20 at 10:43