-1

I have a need for a Check Constraint on a single column that is used to express dollars paid for a service/product.

I want this check constraint to check to make sure there are no leading spaces (e.g. LEFT(1)<>''), no tailing spaces (e.g. RIGHT(1)<>''), that there is no scientific notation, no commas, no NULL values, and has no more than 4 digits AFTER the decimal (e.g. 1234567890.0000)

Adriaan
  • 17,741
  • 7
  • 42
  • 75
Hukd
  • 35
  • 7

1 Answers1

0

Most of the things that you want to check, can instead be just prevented if you make the column of money type instead of having it as varchar and allowing user to enter all sort of weird data. You can use following to alter the column:

Alter table tablename alter column columnname money; -- you can only do this if there is no constraint on it already and there is not data (or the data follows the money type rules)

If you decide to keep it varchar, then the check constraint will be very complicated. To check for left, right spaces and number of digits after decimal, you can use following constraint:

CONSTRAINT chk CHECK(LEFT([paid], 1) != ' ' AND  RIGHT([paid], 1) != ' '  AND LEN(SUBSTRING([paid], CHARINDEX('.', [paid])+1, LEN([paid]))) !> 4 AND paid like '%[^0-9]%')

Nulls can be prevented by simply putting, "not null" when creating the table in front of columns. Hope this gives you an idea. You can use regex expression for preventing commas and scientific notations. Still, I'd prefer using correct data type.

sanmis
  • 515
  • 1
  • 7
  • 22
  • Thanks for such a quick response! I agree, making the column the proper data type is best practice. However, long story short, I receive data from a client, usually in .txt format. I then have an automated process that pulls the data in, and loads it to a specific database as source data. I then have another automated process that runs some analytics on said data. Instead of manually formatting each column back into the proper data type, I'd like to just run a quick check to ensure all values are acceptable. Can you elaborate on this regex expression? – Hukd Mar 19 '15 at 14:01
  • So, if I consider anything other than digits (0-9) as special symbol, I can use "like" to check for these symbols. See my edit above. It means anything which is not digits 0-9 appearing anything in there will be checked. It will check everything including commas, spaces, $, &, etc. You can also change this. E.g. if you think characters need not to be checked just make it '%[0-9A-za-z]%. – sanmis Mar 20 '15 at 00:23