How to get only numeric column values?

Try using the WHERE clause:

SELECT column1 FROM table WHERE Isnumeric(column1);

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.

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 != ''