0

I need to write a constraint in db2 where the username has to start with @ followed by alphanumeric pattern, can have maximum length of 31 and can only contain lowercase alphabets, numbers and _ (underscore) symbol. I did this:

ALTER  TABLE KUNDENKONTAKTDATEN ADD CONSTRAINT
  TwitterID_CHECK CHECK ( trim(length(TWITTER_ID)) <= 31 AND TWITTER_ID like '@%')

My problem is for the part only containing lowercase alphabets, numbers and _ .

This is my homework question and the tips were to use functions like Trim, Translate, Length and Substr but I am allowed to use other functions. Internet told me use fn:matches function but I am not sure how it works.

Can anybody please help me solve this with or without using fn:matches?

Thank you in advance

Edit: Not actually a duplicate to this but similar method can be applied to check if it contains illegal value. This question is not only about finding if a column consists special characters. Parts of my question that are unique are: length, starting with, not finding if it has special character but allowing only one special character (_) as a value and therefore not a duplicate.

Community
  • 1
  • 1
MessitÖzil
  • 1,298
  • 4
  • 13
  • 23
  • 1
    HINT: if you translate all the allowed characters to space and then see if there are non space characters you will know it contained illegal characters – Hogan May 26 '16 at 17:18
  • as a side note, it is **INCREDIBLY DUMB AND POOR DESIGN** to only put these kind of business rules as constraints on a database field. I recommend asking your professor to explain why I would say that if you don't already know. Understanding that is much more important than being able to code the constraint or do a google search on multi-tier architecture. – Hogan May 26 '16 at 17:22
  • 1
    Possible duplicate of [How to find special characters in DB2?](http://stackoverflow.com/questions/17462802/how-to-find-special-characters-in-db2) – mustaccio May 26 '16 at 17:33

1 Answers1

0

Finally this solved the problem.

ALTER TABLE KUNDENKONTAKTDATEN
ADD CONSTRAINT
TwitterID_CHECK CHECK (trim(length(TWITTER_ID)) <= 31 AND TWITTER_ID LIKE '@%' 
AND LENGTH(TRIM(TRANSLATE(TWITTER_ID, ' ',
'abcdefghijklmnopqrstuvwxyz0123456789_@'))) = 0)

Thanks to @Hogan and @mustaccio

MessitÖzil
  • 1,298
  • 4
  • 13
  • 23