Rob Rob - 6 months ago 8
SQL Question

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.

Answer

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