SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast pwaustin's Avatar
    Join Date
    May 2005
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Check for integer

    I have a field that is a VARCHAR and most of the records are integers, however some of them are strings. Is there a way in a SELECT statement to only include the records where the field is an integer?

    Thanks!
    Peter W Austin
    PWA Web Solutions
    www.pwaustin.com

  2. #2
    SitePoint Zealot skyhawk133's Avatar
    Join Date
    Sep 2005
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use the pattern matching to select any field with a numeric value. Something like this should work:

    SELECT * FROM table WHERE field LIKE '[0-9]%';

    That will fetch '1', '10', '1 Fish' but not 'This 1 time'

  3. #3
    SitePoint Enthusiast pwaustin's Avatar
    Join Date
    May 2005
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that didn't quite work, but your post pointed me in the right direction to find this, which does work:

    SELECT * FROM table WHERE field REGEXP '[0-9]%';

    Except, I realize now that that isn't exactly what I need. What I need is for it to convert the field in the query to INT (just like the int_val function does in PHP). So it would turn "175_FR" into "175". The problem is that I am doing an ORDER BY on the field, but since it's a VARCHAR, it will show 99 as the last record instead of 175.
    Peter W Austin
    PWA Web Solutions
    www.pwaustin.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try this --

    ORDER BY varcharfield + 0
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast pwaustin's Avatar
    Join Date
    May 2005
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesome! Exactly what I needed, thanks!
    Peter W Austin
    PWA Web Solutions
    www.pwaustin.com


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
  •