CODEWITHSUNDEEP
sql-server
Gopal
Gopal

Reputation: 11992

How to get only numeric column values?

Using SQL Server 2005

I want to get only numeric values from the table

Column1

12345
asdf
2312
ase
acd
...,

Tried Query

Select Isnumeric(column1) from table

Showing Result as

1
0
1
0
0
..,

I need the colum1 numeric value

Need SQL Server Query help

Upvotes: 32

Views: 201167

Answers (5)

Arshad Alam
Arshad Alam

Reputation: 69

Use This [Tested]

To get numeric

SELECT column1
FROM table
WHERE Isnumeric(column1) = 1; // will return Numeric values

To get non-numeric

SELECT column1
FROM table
WHERE Isnumeric(column1) = 0; // will return non-numeric values

Upvotes: 2

theteague
theteague

Reputation: 413

SELECT column1 FROM table WHERE column1 not like '%[0-9]%'

Removing the '^' did it for me. I'm looking at a varchar field and when I included the ^ it excluded all of my non-numerics which is exactly what I didn't want. So, by removing ^ I only got non-numeric values back.

Upvotes: 0

Breandán
Breandán

Reputation: 1883

SELECT column1 FROM table WHERE ISNUMERIC(column1) = 1

Note, as Damien_The_Unbeliever has pointed out, this will include any valid numeric type.

To filter out columns containing non-digit characters (and empty strings), you could use

SELECT column1 FROM table WHERE column1 not like '%[^0-9]%' and column1 != ''

Upvotes: 76

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

The other answers indicating using IsNumeric in the where clause are correct, as far as they go, but it's important to remember that it returns 1 if the value can be converted to any numeric type. As such, oddities such as "1d3" will make it through the filter.

If you need only values composed of digits, search for that explicitly:

SELECT column1 FROM table WHERE column1 not like '%[^0-9]%'

The above is filtering to reject any column which contains a non-digit character

Note that in any case, you're going to incur a table scan, indexes are useless for this sort of query.

Upvotes: 41

outis
outis

Reputation: 77450

Try using the WHERE clause:

SELECT column1 FROM table WHERE Isnumeric(column1);

Upvotes: 4

Related Questions