TSQL - Cast string to integer or return default value
Solution 1:
Yes :). Try this:
DECLARE @text AS NVARCHAR(10)
SET @text = '100'
SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END
-- returns 100
SET @text = 'XXX'
SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END
-- returns NULL
ISNUMERIC() has a few issues pointed by Fedor Hajdu.
It returns true for strings like $ (is currency), , or . (both are separators), + and -.
Solution 2:
If you are on SQL Server 2012 (or newer):
Use the TRY_CONVERT function.
If you are on SQL Server 2005, 2008, or 2008 R2:
Create a user defined function. This will avoid the issues that Fedor Hajdu mentioned with regards to currency, fractional numbers, etc:
CREATE FUNCTION dbo.TryConvertInt(@Value varchar(18))
RETURNS int
AS
BEGIN
SET @Value = REPLACE(@Value, ',', '')
IF ISNUMERIC(@Value + 'e0') = 0 RETURN NULL
IF ( CHARINDEX('.', @Value) > 0 AND CONVERT(bigint, PARSENAME(@Value, 1)) <> 0 ) RETURN NULL
DECLARE @I bigint =
CASE
WHEN CHARINDEX('.', @Value) > 0 THEN CONVERT(bigint, PARSENAME(@Value, 2))
ELSE CONVERT(bigint, @Value)
END
IF ABS(@I) > 2147483647 RETURN NULL
RETURN @I
END
GO
-- Testing
DECLARE @Test TABLE(Value nvarchar(50)) -- Result
INSERT INTO @Test SELECT '1234' -- 1234
INSERT INTO @Test SELECT '1,234' -- 1234
INSERT INTO @Test SELECT '1234.0' -- 1234
INSERT INTO @Test SELECT '-1234' -- -1234
INSERT INTO @Test SELECT '$1234' -- NULL
INSERT INTO @Test SELECT '1234e10' -- NULL
INSERT INTO @Test SELECT '1234 5678' -- NULL
INSERT INTO @Test SELECT '123-456' -- NULL
INSERT INTO @Test SELECT '1234.5' -- NULL
INSERT INTO @Test SELECT '123456789000000' -- NULL
INSERT INTO @Test SELECT 'N/A' -- NULL
SELECT Value, dbo.TryConvertInt(Value) FROM @Test
Reference: I used this page extensively when creating my solution.
Solution 3:
I would rather create a function like TryParse or use T-SQL TRY-CATCH block to get what you wanted.
ISNUMERIC doesn't always work as intended. The code given before will fail if you do:
SET @text = '$'
$ sign can be converted to money datatype, so ISNUMERIC() returns true in that case. It will do the same for '-' (minus), ',' (comma) and '.' characters.
Solution 4:
As has been mentioned, you may run into several issues if you use ISNUMERIC:
-- Incorrectly gives 0:
SELECT CASE WHEN ISNUMERIC('-') = 1 THEN CAST('-' AS INT) END
-- Error (conversion failure):
SELECT CASE WHEN ISNUMERIC('$') = 1 THEN CAST('$' AS INT) END
SELECT CASE WHEN ISNUMERIC('4.4') = 1 THEN CAST('4.4' AS INT) END
SELECT CASE WHEN ISNUMERIC('1,300') = 1 THEN CAST('1,300' AS INT) END
-- Error (overflow):
SELECT CASE WHEN ISNUMERIC('9999999999') = 1 THEN CAST('9999999999' AS INT) END
If you want a reliable conversion, you'll need to code one yourself.
Update: My new recommendation would be to use an intermediary test conversion to FLOAT to validate the number. This approach is based on adrianm's comment. The logic can be defined as an inline table-valued function:
CREATE FUNCTION TryConvertInt (@text NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
SELECT
CASE WHEN ISNUMERIC(@text + '.e0') = 1 THEN
CASE WHEN CONVERT(FLOAT, @text) BETWEEN -2147483648 AND 2147483647
THEN CONVERT(INT, @text)
END
END AS [Result]
)
Some tests:
SELECT [Conversion].[Result]
FROM ( VALUES
( '1234' ) -- 1234
, ( '1,234' ) -- NULL
, ( '1234.0' ) -- NULL
, ( '-1234' ) -- -1234
, ( '$1234' ) -- NULL
, ( '1234e10' ) -- NULL
, ( '1234 5678' ) -- NULL
, ( '123-456' ) -- NULL
, ( '1234.5' ) -- NULL
, ( '123456789000000' ) -- NULL
, ( 'N/A' ) -- NULL
, ( '-' ) -- NULL
, ( '$' ) -- NULL
, ( '4.4' ) -- NULL
, ( '1,300' ) -- NULL
, ( '9999999999' ) -- NULL
, ( '00000000000000001234' ) -- 1234
, ( '212110090000000235698741' ) -- NULL
) AS [Source] ([Text])
OUTER APPLY TryConvertInt ([Source].[Text]) AS [Conversion]
Results are similar to Joseph Sturtevant's answer, with the following main differences:
- My logic does not tolerate occurrences of
.or,in order to mimic the behaviour of nativeINTconversions.'1,234'and'1234.0'returnNULL. - Since it does not use local variables, my function can be defined as an inline table-valued function, allowing for better query optimization.
- Joseph's answer can lead to incorrect results due to silent truncations of the argument;
'00000000000000001234'evaluates to12. Increasing the parameter length would result in errors on numbers that overflowBIGINT, such as BBANs (basic bank account numbers) like'212110090000000235698741'.
Withdrawn: The approach below is no longer recommended, as is left just for reference.
The snippet below works on non-negative integers. It checks that your string does not contain any non-digit characters, is not empty, and does not overflow (by exceeding the maximum value for the int type). However, it also gives NULL for valid integers whose length exceeds 10 characters due to leading zeros.
SELECT
CASE WHEN @text NOT LIKE '%[^0-9]%' THEN
CASE WHEN LEN(@text) BETWEEN 1 AND 9
OR LEN(@text) = 10 AND @text <= '2147483647'
THEN CAST (@text AS INT)
END
END
If you want to support any number of leading zeros, use the below. The nested CASE statements, albeit unwieldy, are required to promote short-circuit evaluation and reduce the likelihood of errors (arising, for example, from passing a negative length to LEFT).
SELECT
CASE WHEN @text NOT LIKE '%[^0-9]%' THEN
CASE WHEN LEN(@text) BETWEEN 1 AND 9 THEN CAST (@text AS INT)
WHEN LEN(@text) >= 10 THEN
CASE WHEN LEFT(@text, LEN(@text) - 10) NOT LIKE '%[^0]%'
AND RIGHT(@text, 10) <= '2147483647'
THEN CAST (@text AS INT)
END
END
END
If you want to support positive and negative integers with any number of leading zeros:
SELECT
-- Positive integers (or 0):
CASE WHEN @text NOT LIKE '%[^0-9]%' THEN
CASE WHEN LEN(@text) BETWEEN 1 AND 9 THEN CAST (@text AS INT)
WHEN LEN(@text) >= 10 THEN
CASE WHEN LEFT(@text, LEN(@text) - 10) NOT LIKE '%[^0]%'
AND RIGHT(@text, 10) <= '2147483647'
THEN CAST (@text AS INT)
END
END
-- Negative integers:
WHEN LEFT(@text, 1) = '-' THEN
CASE WHEN RIGHT(@text, LEN(@text) - 1) NOT LIKE '%[^0-9]%' THEN
CASE WHEN LEN(@text) BETWEEN 2 AND 10 THEN CAST (@text AS INT)
WHEN LEN(@text) >= 11 THEN
CASE WHEN SUBSTRING(@text, 2, LEN(@text) - 11) NOT LIKE '%[^0]%'
AND RIGHT(@text, 10) <= '2147483648'
THEN CAST (@text AS INT)
END
END
END
END