frankdux — 2008-06-30T16:19:29-04:00 — #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:
Based on those fields, 3 rows would be returned:
The field's datatype is varchar (25).
r937 — 2008-06-30T23:48:35-04:00 — #2
... WHERE field NOT LIKE '[^0-9]'
frankdux — 2008-07-01T09:32:40-04:00 — #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.
r937 — 2008-07-01T09:44:08-04:00 — #4
try ... NOT LIKE '%[^0-9]%'
longneck — 2008-07-01T09:53:51-04:00 — #5
LIKE won't work. you need REGEXP as the keyword.
this is slightly simpler:
fieldname REGEXP '^[0-9]*$'
r937 — 2008-07-01T10:04:05-04:00 — #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
frankdux — 2008-07-01T10:15:11-04:00 — #7
r937, your second version worked fine for me:
SELECT * from tablename WHERE fieldname NOT LIKE '%[^0-9]%'
longneck — 2008-07-01T12:48:26-04:00 — #8
i've got mysql on the brain.
r937 — 2008-07-01T14:04:10-04:00 — #9
apparently there are pills you can take for that...
jinukjohn1 — 2008-07-09T10:28:18-04:00 — #10
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?
r937 — 2008-07-09T11:18:17-04:00 — #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
jinukjohn1 — 2008-07-09T11:24:26-04:00 — #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?
mittineague — 2014-09-24T19:05:38-04:00 — #13
This topic is now archived. It is frozen and cannot be changed in any way.