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

Question

Basically I want to do this:

I want to return a recordset that converts a single nvarchar (ID) value to an integer if it contains a number. If the identifier can be converted to a number, add this line to the SELECT recordset. If not, skip this line.

I think SQL should look something like this.

(ID - 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 is 'Default' for int data type. If I do SELECT CAST(ID as int) , which also does not work.

Answer 1

To be safe, forget about ISNUMERIC

If you do not expect 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 must be ISNumeric(Id)=1

However ISNUMERIC has a problem

run it

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

All are returning 1

Take a look at IsNumeric, IsInt, IsNumber to find out if it can be converted to an integer

Answer 3

Perhaps this may help:

 select column_desired from requested_table <> 0 or is not null