CODEWITHSUNDEEP
sqlsql-server
Muhammad Farhan
Muhammad Farhan

Reputation: 69

Sql Convert value to int if possible, if not possible set value to 0

Need Sql query which Convert value to int if possible, if not possible set value to 0.

SELECT IIF((isnumeric('11961630')),TRY_PARSE('11961630' as int), 0) => it should return 11961630
    SELECT IIF((isnumeric('CH11961630')),TRY_PARSE('CH11961630' as int), 0) => it should return 0

Upvotes: 4

Views: 1489

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

In case any reader would want a way to do this without using TRY_PARSE or any other of the more recent fancy conversion functions, it is possible to do this using only LIKE and CAST:

SELECT
    input,
    CASE WHEN input NOT LIKE '%[^0-9]%' AND
                    CAST(input AS DECIMAL(10,0)) BETWEEN -2147483648 AND 2147483647
         THEN CAST(input AS INT) ELSE 0 END AS output
FROM yourTable;

Upvotes: 1

ekolis
ekolis

Reputation: 6786

select isnull(try_parse('42' as int), 0) -- returns 42
select isnull(try_parse('fred' as int), 0) -- returns 0

Upvotes: 9

Related Questions