CODEWITHSUNDEEP
sqlsql-server
Rob
Rob

Reputation: 195

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

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.

Upvotes: 8

Views: 45611

Answers (3)

Dragos
Dragos

Reputation: 1

Maybe this one could help:

select column_desired from requested_table <> 0 or is not null

Upvotes: -1

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

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

Upvotes: 16

SQLMenace
SQLMenace

Reputation: 135021

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

Upvotes: 12

Related Questions