Search code examples
sqlsubstringcheck-constraints

SQL Check constraint on substrings

Forgive me, if this problem is solved in another ticket on SO... I've been searching, but can't seem to find quite the right solution...

I am creating a table. Column 'Creditor' is numeric all the way, EXCEPT that the very last char may be a dash.

This means that examples like '1234-', '92846293' and so on, are valid, and that '12354-53', '12345K' are invalid. The string length is not fixed (except it's a varchar(50)).

I don't know how to create the check constraint.

Please help!

Solution

  • You did not state your DBMS. For PostgreSQL this would be:

    alter table foo 
  add constraint check_creditor check (creditor ~ '^([0-9]+)\-?$');

    For Oracle this would be:

    alter table foo 
   add constraint check_creditor check (regexp_like(creditor, '^([0-9]+)\-?$'))

    If your DBMS supports regular expressions, you will need to use the syntax for your DBMS to check this. The regular expression itself '^([0-9]+)\-$' will most probably be the same though.