How to select only records that have number in column (SQL)

Question

Basically I want to do this:

I want to return a record set converting one nvarchar value (ID) to an integer if it contains a number. If ID can be converted to a number, then add that row to the SELECT record set. If not, skip that row.

I think the SQL should look something like this.

(ID is nvarchar(10) in dbo.Table)

CREATE TABLE #Temp  (ID int)
INSERT INTO #Temp SELECT ID FROM Table Where ISNumeric(Id)=0

But I get an error: nvarchar value 'Default' to data type int. If I do a SELECT CAST(ID as int) that does not work either.

Answer 1

To be safe, forget about ISNUMERIC

If you are not expecting negative numbers, you can use this

INSERT INTO #Temp SELECT ID FROM Table
Where Id > ''  -- is not the empty string and is not null
  AND not ID like '%[^0-9]%'  -- contains digits only

Answer 2

ISNumeric(Id)=0 should be ISNumeric(Id)=1

However ISNUMERIC has a problem

run this

SELECT ISNUMERIC('2d5'), ISNUMERIC('-'), ISNUMERIC('+')

Those all return 1

Take a look at IsNumeric, IsInt, IsNumber for some ways to figure out if it can be converted to an integer