Check constraint on digits in a string in sql

Question

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

score 2 · Accepted Answer · 2015-10-25 08:19:57Z

2

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.

edited Oct 25, 2015 at 8:19

answered Oct 24, 2015 at 22:49

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Sarthak Over a year ago

select regexp_replace('abcd123','[^0-9]',''); gives output bcd123 in postgres. Expected output:123

user330315 Over a year ago

@user3398648: sorry, forgot the 'g' option, corrected

Collectives™ on Stack Overflow

Check constraint on digits in a string in sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related