I have the following check constraint on an NVARCHAR(255) column:

([weekReviewWk10]='A' OR [weekReviewWk10]>=(0) AND [weekReviewWk10]<=(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'.

  • 2
    How to fix it? Proper database design :-) – dnoeth Jan 19 '16 at 8:09
  • 2
    "The column has to be NVARCHAR since, as you can see, I also need my users to be able to enter 'A'." Really? And you don't mind that they can also enter '23.5.76' or '23.5.7A,bc' or '2A.5B.C76'. i.e. nonsense data? – ypercubeᵀᴹ Jan 19 '16 at 8:43
  • These are the business processes we have - I can't change them. We have a frontend in Access where teachers enter marks for tasks and they use 'A' to indicate absence. They enter the date through a datasheet. I could try and point the datasheet to update two different tables depending on it being numeric or char, but that would be difficult in Access. – Ben Jan 19 '16 at 9:15

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

  • 3
    They have numeric values in a text column, which is equally bad of course ;) – ypercubeᵀᴹ Jan 19 '16 at 7:51
  • It says "can't validate check constraint". – Ben Jan 19 '16 at 9:18
  • @fluffybonkers sorry, had the wrong column name. I've updated my answer. – Daniel Hutmacher Jan 19 '16 at 9:30
  • I know. I saw that and changed the column name myself, however, I get the same error. – Ben Jan 19 '16 at 9:39
  • It seems to be because I had square brackets around the field names... – Ben Jan 19 '16 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.