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)
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.