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

Question

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).

possible duplicate of How to only select numeric data from mysql? — Kermit
– Kermit, Commented Jan 28, 2013 at 16:09
classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html — Tim Schmelter
– Tim Schmelter, Commented Jan 28, 2013 at 16:11

UnitStack · Accepted Answer · 2013-01-28 16:14:42Z

5

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

answered Jan 28, 2013 at 16:14

UnitStack

1,1851 gold badge11 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harke Over a year ago

It worked. Thanks. But could you explain how it worked. What does ISNUMERIC(Value+'d0') do? What significance does 'd0' have?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related