how to select only the int values in a column that is nvarchar

Question

Possible Duplicate:
CAST and IsNumeric

I have a database column that is nvarchar, but i want to only get the rows of the database that have integers in that column field is it possible?

Explain "have integers" - if the value is "A1", should you get the row? Or are you looking for rows where the column value is entirely integers? — OMG Ponies, Apr 08 '11 at 14:38
The row has to be completely integers, what happened was i imported an excel sheet and the column that i need to use to join two tables must be an int. — whatsupprogrammers, Apr 08 '11 at 14:41

score 14 · Accepted Answer · answered Apr 08 '11 at 14:43

14

Adding the '.0e0' to the end of the column in the ISNUMERIC check will ensure that only integers will be found.

SELECT *
    FROM YourTable
    WHERE ISNUMERIC(YourColumn + '.0e0') = 1

answered Apr 08 '11 at 14:43

Joe Stefanelli

+1 Don't forget that ISNUMERIC('' + '.0e0') = 1 – Alex K. Apr 08 '11 at 14:50
suggested revision taking Alex's suggestion into consideration - evaluate both the column and the `column + '.0e0'` which should cover both cases. – JNK Apr 08 '11 at 14:57
`SELECT ISNUMERIC('-' + '.0e0')` and `ISNUMERIC('+' + '.0e0')` return 1 also (as well as all the white space characters) – Martin Smith Apr 08 '11 at 14:57
`isnumeric` doesn't guarantee that an int will fit the number. it could be a bigint or a larger numeric. Example: 3,000,000,000 won't fit an int, and precision 20 numbers won't fit a bigint. – Gregory Klopper Apr 12 '13 at 19:05
A more complex check is: `SELECT * FROM YourTable WHERE CASE WHEN ISNUMERIC(LTRIM(RTRIM(YourColumn)) + '.0e0') = 1 THEN CASE WHEN LTRIM(RTRIM(YourColumn))!='+' AND LTRIM(RTRIM(YourColumn))!='' AND ABS(CAST(LTRIM(RTRIM(YourColumn)) AS DECIMAL(25,0)))<=2147483647 THEN 1 ELSE 0 END ELSE 0 END = 1` – Gregory Klopper Apr 12 '13 at 19:06

score 0 · Answer 2 · answered Apr 08 '11 at 14:42

0

You check for isnumeric:

select evalcolumn from table where isnumeric(evalcolumn) = 1

answered Apr 08 '11 at 14:42

Adriano Carneiro

`ISNUMERIC` has a lot of caveats - Joe's answer is better since it checks for `INT` and not just for a number. Your method will allow values like `1e2` as numeric. – JNK Apr 08 '11 at 14:46
`select isnumeric('3d8')` = 1 – Tim Schmelter Apr 08 '11 at 14:47
That's true. One more lesson learned! – Adriano Carneiro Apr 08 '11 at 14:49

2 Answers2