SQL Server 2014: How to cast a string value to int only if the value can be casted?

Question

I try to cast a string value in a field of a table to int, but only in the case that the value stores a value that can be casted to int (in the other case the original value must be returned). Example:

   DECLARE @ErrorCode nvarchar(1024)
   SET @ErrorCode = 'a10'
   SELECT IIF(TRY_CAST(@ErrorCode AS int) IS NULL, @ErrorCode, CAST(@ErrorCode AS int))

I've seen similar codes on StackOverflow.

My problem is that the SQL Server (2014) does not seem to short-circuit, and executes the cast always, even if TRY_CAST(@ErrorCode AS int) results in NULL. The result of the code above is the error "Conversion failed when converting the nvarchar value 'a10' to data type int."

See this sample on rextester.com

I also tried other variants with the same result:

SELECT CASE WHEN TRY_CAST(@ErrorCode AS int) IS NULL THEN @ErrorCode ELSE (SELECT CAST(@ErrorCode AS int)) END
SELECT CASE TRY_CAST(@ErrorCode AS int) WHEN 1 THEN CAST(@ErrorCode AS int) ELSE @ErrorCode END

How can I achieve my goal (avoid the cast in case the value in @ErrorCode cannot be casted)?

You can use `ISNUMERIC()` function to check if the string can be parsed — BytesOfMetal, Nov 17 '16 at 10:12
show some sample data. The issue i see here, is that a column can only have 1 type. so wanting to return an int if it's an int or another type in the same column won't work. How do you plan to deal with this? — Tanner, Nov 17 '16 at 10:12
I'm not an MS SQL Server expert but I would check the value against a regex if it only includes digits before it's casted to an int. — Rene M., Nov 17 '16 at 10:13
It doesn't make sense. you have to decide on a type for the whole expression and obviously it can't be int. — David דודו Markovitz, Nov 17 '16 at 10:17
@msanz: ISNUMERIC causes the same error and additionally would result in true for some values like '$1' that cannot be casted. — Jürgen Bayer, Nov 17 '16 at 10:20
@Tanner & Dudu Markovitz: Sure, but that is not the issue. I altered the code to cast the result of the cast to varchar. I get the same conversion error. @ Rene M.: That's not the issue. TRY_CAST works perfectly. It's the CAST to int that causes the error if the value cannot be casted. — Jürgen Bayer, Nov 17 '16 at 10:22
Take a look to this answer: http://stackoverflow.com/a/14720149/6009117 — BytesOfMetal, Nov 17 '16 at 10:23
there's one more thing: you should not compare '= null', the correct is 'is null', run these and see the difference: `SELECT IIF(TRY_CAST(@ErrorCode AS int) = NULL, 'not number', 'number')` `SELECT IIF(TRY_CAST(@ErrorCode AS int) IS NULL, 'not number', 'number')` — Ricardo Pontual, Nov 17 '16 at 10:26
This is exactly the issue. The type of your conditional expressions (IIF / CASE) is INT regardless of your actual data. If your expression returns something that can't be converted to the result type than you fail. — David דודו Markovitz, Nov 17 '16 at 10:29
I corrected the error in the code in the question that I used = for the comparison with null, and that the code then really created the error. The accepted answer and the answer from Prdp solve the issue. — Jürgen Bayer, Nov 17 '16 at 12:32

Pரதீப் · Answer 1 · 2016-11-17T10:22:05.247

1

To validate NULL you should IS NULL

DECLARE @ErrorCode NVARCHAR(1024)

SET @ErrorCode = 'a10'

SELECT IIF(TRY_CAST(@ErrorCode AS int) IS NULL, @ErrorCode, CAST(CAST(@ErrorCode AS int) AS VARCHAR(50)))

You need to convert the INT again to VARCHAR again to avoid the implicit conversion. IIF returns the data type with the highest precedence from the types in true_value and false_value.

edited Nov 17 '16 at 10:22

answered Nov 17 '16 at 10:16

Pரதீப்

91,748
19
131
172

1

Ups. I made a mistake in my query. Your answer works. Don't know why it was downvoted. – Jürgen Bayer Nov 17 '16 at 12:09

Zohar Peled · Accepted Answer · 2016-11-17T10:31:09.460

1

The simple solution would be to use COALSECE:

DECLARE @ErrorCode nvarchar(1024)
SET @ErrorCode = 'a10'
SELECT COALSECE(CAST(TRY_CAST(@ErrorCode AS int) as nvarchar(1024)), @ErrorCode)

However, I don't see the point of casting to int and then back to nvarchar.

edited Nov 17 '16 at 10:31

answered Nov 17 '16 at 10:21

Zohar Peled

79,642
10
69
121

The point is that I have to cast values like '0000' to '0' to later be able to query for '0'. Due to the implemented solution that uses a rule engine I cannot cast values when querying. I need one value that I can check for. I edited the code in the question to cast the int value back to nvarchar to get the same type. – Jürgen Bayer Nov 17 '16 at 10:25
I see. but it's worth mentioning that if the value is `000a` it will still be `000a`. If you want to trim leading zeros even for non numeric values you will have to do it in a different way. – Zohar Peled Nov 17 '16 at 10:28
Yes. But in this case I do not need to trim leading zeros. It's only for numbers that can exist in the table. – Jürgen Bayer Nov 17 '16 at 10:32

score 0 · Answer 3 · answered Nov 17 '16 at 10:22

The problem I see is trying to hold 2 different types in a single column. I'm not sure how you plan to use this information, but you could split the values in to numeric and text columns based on the type. You can do an ISNUMERIC() check and CAST to INT if it's true, otherwise leave it as text, like so:

CREATE TABLE #ErrorCodes ( ErrorCode NVARCHAR(10) )

INSERT  INTO #ErrorCodes
        ( ErrorCode )
VALUES  ( '123' ),
        ( 'a10' ),
        ( 'bbb' ),
        ( '456' )

SELECT  ErrorCode AS OriginalVal ,
        CASE WHEN ISNUMERIC(ErrorCode) = 1 THEN CAST(ErrorCode AS INT)
             ELSE NULL
        END AS NumericVal ,
        CASE WHEN ISNUMERIC(ErrorCode) = 0 THEN ErrorCode
             ELSE NULL
        END AS NonNumericVal
FROM    #ErrorCodes

DROP TABLE #ErrorCodes

Produces:

OriginalVal NumericVal  NonNumericVal
=====================================
123         123         NULL
a10         NULL        a10
bbb         NULL        bbb
456         456         NULL

SQL Server 2014: How to cast a string value to int only if the value can be casted?

3 Answers3