Check Constraints on Decimals In SQL Server 2012 I have the following check constraint on an NVARCHAR(255) column:

([weekReviewWk10]='A' OR [weekReviewWk10]&gt;=(0) AND [weekReviewWk10]&lt;=(25))

However, when someone enters, say, 23.5, SQL Server rejects the input. How can I fix this? I know it is because it is testing things as ints, but how do I get it to test the entry as a decimal?

The column has to be NVARCHAR since, as you can see, I also need my users to be able to enter 'A'.
You could use TRY_CAST to cast the column to a decimal format before comparing.

weekReviewWk10='A' OR
TRY_CAST(weekReviewWk10 AS decimal(3, 1)) BETWEEN 0.0 AND 25.0


If the cast fails, the result of TRY_CAST will be NULL and the comparison is false, but it won't crash. TRY_CAST requires SQL Server 2012 or newer.

It's worth mentioning that you're potentially opening up a pit of bad things when you allow text values in a numeric column, but judging from the question, you already know that. :)