Using SQL Server, how can I do a query that returns results only where a field is entirely numbers? If the field has part alpha or all alpha, it should not be in the results. Here is a sample of what the fields look like:
SELECT * from tablename WHERE fieldname NOT LIKE ‘[^0-9]’
but it doesn’t work. It still returns all rows whether the field has all numbers (1234567), mix of numbers and letters (123dog) or all letters (dogpound). It should only return rows where the field has all numbers.
I tried the SELECT * from tablename WHERE fieldname NOT LIKE ‘%[^0-9]%’, however it didn’t help me. I tried SELECT * from tablename WHERE fieldname NOT LIKE ‘%[A-Z]%’ and it worked. But I can’t use the same query in DB2 select statement. Any pointers?
Thanks a lot r937 for your inputs. Looks like I need to use a query like
SELECT * from tablename WHERE fieldname NOT LIKE "%A%’
AND fieldname NOT LIKE "%B%’
…
… and so on till ‘%Z%’
Is there any better way than this?