TRY_CAST fails when fed very long string... but only from column or variable

Question

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

SELECT TRY_CAST(@string AS INT);

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

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:

SELECT DATALENGTH(TRY_CAST(REPLICATE('x', 4001) AS nvarchar(max)));

----------
8002

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';
PRINT @x;

----
t

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

----------
NULL

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