CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005
Harke
Harke

Reputation: 1289

How do I get only the numeric values in SQL Server (ISNUMERIC not giving correct values)

I have a table called 'testTable' with two columns, 'id' that is auto incremented and 'someValue'.

The data contained in the 'someValue' column are: 12, 1.2, .4, 1d4, +, -, .

I want to select only the numeric values. But when I use the following query:

SELECT someValue, ISNUMERIC(someValue) 
FROM testTable;

all the values are true. And for the query:

SELECT * FROM testTable 
WHERE ISNUMERIC(someValue) = 1;

all the values are being returned.

I just want 12, 1.2 and .4 .

How can I achieve this?

UPDATE:

The data type for the column someValue is varchar(50).

Upvotes: 1

Views: 2344

Answers (1)

UnitStack
UnitStack

Reputation: 1185

SETUP:

CREATE TABLE SomeTable
(
  ID INT,
  Value NVARCHAR(250)
)


INSERT INTO SomeTable (ID, Value) VALUES (1, '12')
INSERT INTO SomeTable (ID, Value) VALUES (2, '1.2')
INSERT INTO SomeTable (ID, Value) VALUES (3, '.4')
INSERT INTO SomeTable (ID, Value) VALUES (4, '1d4')
INSERT INTO SomeTable (ID, Value) VALUES (5, '+')
INSERT INTO SomeTable (ID, Value) VALUES (6, '-')
INSERT INTO SomeTable (ID, Value) VALUES (7, '.')

QUERY:

SELECT * FROM SomeTable WHERE ISNUMERIC(Value+'d0')=1

RESULT:

ID  VALUE
1   12
2   1.2
3   .4

Upvotes: 5

Related Questions