TRY_CAST('<VALID NUMBER>' AS INT) returns NULL, would expect the number itself

Lou Driehuis 56 Reputation points
2023-05-24T09:00:05.1666667+00:00

Hi,

When converting a (N)VARCHAR number with TRY_CAST to INT the result is NULL.

TRY_PARSE returns the right result.

Example:

SELECT	TryCastNumber	= TRY_CAST	(14.0 AS INT)
	,	TryCastVARCHAR	= TRY_CAST	('14.0' AS INT)
	,	TryParseVARCHAR = TRY_PARSE	('14.0' AS INT)

Returns:

TryCastnumber = 14, -- As expected

TryCastVARCHAR = NULL, -- Expected 14!!!

TryParseVARCHAR = 14 -- As expected

Is this correct behaviour?

Many thanks

Lou Driehuis

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,947 questions
0 comments No comments
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,501 Reputation points Microsoft Vendor
    2023-05-24T09:26:15.75+00:00

    Hi @Lou Driehuis

    I did a simple test.

    select CastVARCHAR	= CAST	('14.0' AS INT)
    

    An error is reported.

    User's image

    If you want to get 14, you can try replacing int with DECIMAL(2,0).

     SELECT	TryCastNumber	= TRY_CAST	(14.0 AS INT)
    	,	TryCastVARCHAR	= TRY_CAST	('14.0' AS DECIMAL(2,0))
    	,	TryParseVARCHAR = TRY_PARSE	('14.0' AS INT)
    

    Output:

    User's image

    Best regards,

    Percy Tang


    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1 additional answer

Sort by: Most helpful
  1. Olaf Helper 44,931 Reputation points
    2023-05-24T09:22:00.99+00:00
    SELECT CAST('14.0' as int)
    
    

    ... raises an error, so it is an expected behaviour of TRY_CAST

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.