#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    49
    Rep Power
    14

    Bring back just numeric


    Hi,


    Is there a sql statement that will bring back all records that are just numeric? A database column has both numeric and string values stored in it. I need to bring back just the numeric values.
    Auction off your web site real estate.

    http://www.webmasterbids.com
  2. #2
  3. An Ominous Coward
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2002
    Posts
    4,425
    Rep Power
    0
    What database system?
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62

    Re: Bring back just numeric


    Originally posted by phpnewbie3
    A database column has both numeric and string values stored in it.
    Then it ONLY has strings in it, because the column would have to be a text or char type. You must perform tests to find out which values are candidates for conversion to numeric. Are you looking for integers, or integers and floating-point values?

    Almost every database system out there has operators for casting values of one type to another type. One simple test you could do is simply cast the value to INT or FLOAT in your query, and if the resulting value is non-zero, then in must have been a numeric one. However, this doesn't answer some other questions: Are there limits to the size you will accept? what if you have both string and numeric characters in one row? Will you accept just the numeric part as a value, or does ANY other string content disqualify the row? What about multiple numbers in a rwo?

    I don't know why you would need this sort of query, or even why the database has been designed in such a way as to need this query. If the values in this column are significant to the logic of your application, surely you can design the database in a more efficient and logical way. Two different types of data should be in two different columns. or perhaps even in two different tables.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    49
    Rep Power
    14
    Thanks for your replies,

    It is a MYSQL database and I am using PHP. You're right rycamor about having two different types of data in the same column, unforunetly, I didn't design the database so I don't have much control over that.

    The column is set as varchar100
    Auction off your web site real estate.

    http://www.webmasterbids.com
  8. #5
  9. An Ominous Coward
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2002
    Posts
    4,425
    Rep Power
    0
    I was thinking along the lines of something like this in the record:

    "I said 20, she said 21"

    and now they want to pull just the records with numbers in the text. And the solution I was thinking is:

    Regular Expressions! Hooray!

    Disclaimer: I don't use MySql much, and I don't know anything about the RegExp engine it uses.... it also doesn't solve many of the problems Rycamor brought up.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    ya know, i've seen that mysql page before, and it sure looks like all it can do is search -- i.e. return a true/false indicator to show whether the string actually matches the regular expression

    i have not seen any examples where the mysql regexp is used to modify a string, e.g. strip non-numerics out

    anyone know for sure?


    rudy

IMN logo majestic logo threadwatch logo seochat tools logo