CODEWITHSUNDEEP
sqlpostgresql
Sarthak
Sarthak

Reputation: 1168

Check constraint on digits in a string in sql

I have a table column p which should only include entries like Cash_1, Cash_2, Cash_3 till Cash_36. I am not able to use this as a check constraint. I tried check((p>='Cash_1' AND p<='Cash_36') AND p like 'Cash_%') but i am not able to enter the data like Cash_4 or Cash_5. Even if i do so i am not able to ensure that the string after 'Cash_' is a digit Plz help

Upvotes: 0

Views: 822

Answers (1)

user330315
user330315

Reputation:

You need to combine two checks for this.

You need a regular expression to verify that the value Cash_ is followed by one or two digits:

check (p ~ 'Cash_[0-9]{1,2}')

However this also allows e.g. Cash_00 or Cash_99. So you need another condition that checks if the number is between 1 and 36

check (p ~ 'Cash_[0-9]{1,2}' and regexp_replace(p, '[^0-9]', '', 'g')::int between 1 and 36)

regexp_replace(p, '[^0-9]', '') removes all non-digits from the string and the result is then cast to an integer using ::int. This number is then checked for the proper interval.

Upvotes: 2

Related Questions