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

To avoid issues with ISNUMERIC and all spaces, -, +, . etc, use the fact that the column is char(5)

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

Edit: for any number of characters. Double negative...

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

Answer 3

Use positive and negative checks to make sure we have an integer: It must contain a digit. Only digits and spaces are allowed. No spaces are allowed between digits.

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