Select only integers from char column using SQL Server

Question

How can I write a select statement to select only integers (and nothing more) from a char column in SQL Server. For example, my table name is POWDER with 2 columns, ID (int) and Name(char (5))

ID     Name
-- ----------
1     AXF22
2     HYWWW
3     24680
4     8YUH8
5     96635

I want to be able to select only those rows that contain an integer and nothing more (ID 3 and ID 5 in this example)

If I try:

SELECT * 
  FROM POWDER
 WHERE Name LIKE '[0-9]%'

...it will return:

ID     Name
--    ----------
3      24680
4      8YUH8
5      96635

Any ideas how to get the rows containing just integers?

Answer 1

SELECT * FROM POWDER WHERE IsNumeric(Name) = 1

IsNumeric returns 1 for some other characters that are valid in numbers, such as + and - and $ but for your input you should be fine.

Answer 2

Try this:

SELECT * FROM Table WHERE Name LIKE '[0-9]%%'