Skip to main content
Added feedback link
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663
added 619 characters in body
Source Link
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

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.

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.

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.

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.

added 619 characters in body
Source Link
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

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.

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

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

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

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.

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.

Source Link
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619
Loading