I've recently encountered this weird behavior of TRY_CAST function, and I wonder what's going on here. (It feels like bug, but most of such bugs I have encountered in past were actually intentional.)

When I use the function on string column or variable longer than 4000 characters, I get an error: String or binary data would be truncated.

DECLARE @string NVARCHAR(MAX) = REPLICATE('x', 4001); 


The weird thing is, when I take the literal string and put it into the function, it works fine (it returns NULL).

What is happening here?

I am parsing through a history table, so I need to be able to consume all kinds of strings (and look something up for integer values).


1 Answer 1


While the documentation says "any valid expression," I don't think it's quite true. And unfortunately this is one of those cases where the error message about length happens before TRY_CAST is ever able to gracefully handle your input.

Even though it is clear that TRY_CAST will accept strings longer than 4000:



So it seems the function's ability to gracefully handle the input depends in part on the destination type. It might be that a string constant gets silently truncated, similar to:

DECLARE @x varchar(1) = 'this is more than one character';


But if you tried to put that into a column:

CREATE TABLE #x(x varchar(1));
INSERT #x(x) VALUES('this is more than one character');

Msg 2628, Level 16, State 1
String or binary data would be truncated in table 'tempdb.dbo.#x_...', column 'x'. Truncated value: 't'.
Msg 3621, Level 0, State 0
The statement has been terminated.

Same string input, but handled in very different ways.

Now, it is questionable why you would ever try to cast a string this long to an int in the first place, because the longest a valid and reasonable value could be would be ~40 characters, e.g.

SELECT TRY_CAST(0.00000000000000000000000000000000000001 AS int);

If the goal of the code is to convert any string on earth to an int, there's really no reason to allow more than 40 characters.

(I guess in theory you could try to cast someone's string input of π as an int, but, we know the answer is 3, and SQL Server can't express all those decimals anyway - by default PI() only returns 15 decimal places.)

If you really can't control the input, then I would simply wrap the argument in LEFT, e.g.

SELECT TRY_CAST(LEFT(@string, 4000) AS int);

You could use a wrapper function and all that, but I don't think you're going to find a simpler workaround. I might suggest instead, though TRY_CAST also fails in similar ways here, not handling an overflow:

DECLARE @string nvarchar(max) = N'222299992929';

SELECT CASE WHEN @string > 40 THEN 'What are you thinking?'
  ELSE CONVERT(varchar(11), TRY_CAST(@string AS int)) END;

Msg 248, Level 16, State 1
The conversion of the nvarchar value '222299992929' overflowed an int column.

Which has to do with the CASE expression and data type precedence, since this works fine:

DECLARE @string nvarchar(max) = N'222299992929';

SELECT TRY_CAST(@string AS int);


At the end of the day, this is a "doctor, it hurts when I do this" scenario.

Existing Microsoft feedback: TRY_CAST should not throw error on long values


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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