SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL query for no alpha in a field

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    ... WHERE field NOT LIKE '[^0-9]'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    ah, rats

    try ... NOT LIKE '%[^0-9]%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    LIKE won't work. you need REGEXP as the keyword.

    this is slightly simpler:
    Code:
    fieldname REGEXP '^[0-9]*$'
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict frankdux's Avatar
    Join Date
    Apr 2004
    Location
    Ashland, North Carolina
    Posts
    267
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, your second version worked fine for me:

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

    Thanks!

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    longneck, REGEXP she's a no work on SQL Server
    i've got mysql on the brain.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    apparently there are pills you can take for that...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jinukjohn1 View Post
    Hi But I can't use the same query in DB2 select statement. Any pointers?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •