CODEWITHSUNDEEP
sqlcastingint
user2199192
user2199192

Reputation: 175

T-SQL find only the columns which can be casted as INT

I need to write an SELECT statement, that has a WHERE clause, where only the rows that can be CAST as INT will be selected, not BIGINT, but INT.

Is that possible

I am on SQL Server 20**

Right now I am doing something like this:

SELECT CAST(column as INT) FROM TABLE
WHERE ISNUMERIC(column) = 1

But then I get 'The conversion of the varchar value '275949275947' overflowed an int column'

There is alot of different data in the column - but I only need the INTs

Upvotes: 0

Views: 448

Answers (3)

paul
paul

Reputation: 22001

SQL Server 2012:

select try_parse('1231321313' as int)
select try_parse('234242342341231321' as int)

SQL Server < 2012:

select  case when ISNUMERIC('1231321313') = 1 
                and patindex('%[^0-9-]%', '1231321313') = 0
                and cast('1231321313' as bigint) between -2147483648 and 2147483647
                then cast('1231321313' as int) end

select  case when ISNUMERIC('234242342341231321') = 1
                and cast('234242342341231321' as bigint) between -2147483648 and 2147483647
                then cast('234242342341231321' as int) end

edit: to deal with potential currency values ('$23424231321', '€23424231321' etc) you could also add patindex('%[^0-9-]%', '1231321313') = 0

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

This will work on MS SQL 2012+:

DECLARE @t TABLE ( A INT, B NVARCHAR(50) )

INSERT  INTO @t
VALUES  ( 1, '1' ),
        ( 2, '275949275947' ),
        ( 2, 'cc' )


SELECT  CAST(B AS INT) AS B
FROM    @t
WHERE   TRY_PARSE(
        B AS INT) IS NOT NULL

Output:

B
1

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

DECLARE @Table TABLE (
    [column] VARCHAR(200)
)

INSERT INTO @Table SELECT '1'
INSERT INTO @Table SELECT '154674'
INSERT INTO @Table SELECT '-2147483649'
INSERT INTO @Table SELECT '2147483648'

SELECT CAST([column] as INT) FROM @TABLE
WHERE ISNUMERIC([column]) = 1
AND CAST([column] as BIGINT)>=-2147483648
AND CAST([column] as BIGINT)<=2147483647

Upvotes: 0

Related Questions