1

I am inserting values form a staging table in SQL Azure into a production table. Values in the staging table are varchar, while the target value in the production table is tinyint (it is an age field for humans, so I reckon that up to 255 will suffice ;) )

Problem is that due to a bug in the source system, we tend to get some values as -1 or -2 when they should be 0. This causes issues, since TINYINT only supports values form 0 to 255 (and also because this is factually wrong). I was wondering if there is some sort of case statement that I could use to convert values to 0 if they fall under 0, or perhaps if they fall outside the 0-255 range?

5

As TINYINT only allows 0-255 you could also use

ISNULL(TRY_CAST(YourCol AS TINYINT),0)

The TRY_CAST above returns NULL if supplied an invalid input for the datatype (including -ve numbers) and I would be minded to leave it that way.

But if you must change these to 0 you can wrap it in an ISNULL

3

Try something like this

DECLARE @BadData VARCHAR(100) = '-5'
DECLARE @TinyInt TINYINT

SET @TinyInt = CASE 
        WHEN cast(@BadData AS INT) < 0
            THEN 0
        ELSE @BadData
        END

SELECT @TinyInt
  • This seems to work! SELECT CASE WHEN cast([fieldname] AS INT) < 0 THEN 0 ELSE [fieldname] END – pmdci Sep 21 '16 at 11:40
  • Naturally, the result of the CAST still has to be <= 255 or it won't fit into a TinyInt (you'll get an arithmetic overflow) – Scott Hodgin Sep 21 '16 at 11:43

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.