SQL query for no alpha in a field

Databases
1

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:

1234567
dogstar1
223344
applewood
135790
123abc

Based on those fields, 3 rows would be returned:

1234567
223344
135790

The field’s datatype is varchar (25).

2

… WHERE field NOT LIKE ‘[^0-9]’

3

I did the following:

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.

4

ah, rats

try … NOT LIKE ‘%[^0-9]%’

5

LIKE won’t work. you need REGEXP as the keyword.

this is slightly simpler:

fieldname REGEXP '^[0-9]*$'
6

longneck, REGEXP she’s a no work on SQL Server

unlike (no pun intended) my first reply, i tested my second one and it’s fine

7

r937, your second version worked fine for me:

SELECT * from tablename WHERE fieldname NOT LIKE ‘%[^0-9]%’

Thanks!

8

i’ve got mysql on the brain.

9

apparently there are pills you can take for that…

10

Hi r937,

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?

11

i’m afraid not

i did a quick check of the db2 manual and it looks like there is nothing similar to the pattern search in sql server

12

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?