38 users online (0 members and 38 guests)  


  Results 1 to 7 of 7

Related

  1. Replies: 5
  2. comparison to javascript    Forum: CGI Perl Forum
    Replies: 0
  3. IceIsHot.com Web Host Comparison    Forum: Introduce Yourself
    Replies: 2
  4. HTML/CSS Comparison    Forum: HTML Forum
    Replies: 9
  1. #1
    DeadMeatGF's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    381

    === type comparison in mysql?

    How do I avoid getting string values in a query such as
    Code:
    SELECT * FROM table WHERE column = 0
    It's not my database, otherwise I wouldn't allow string and numeric values in the same column
    I'm sure there's a simple answer, but I'm damned if I can find it!

  2. #2
    uthus's Avatar
    Senior Member

    Status
    Offline
    Join Date
    Feb 2006
    Location
    Ft Hood
    Posts
    75

    Re: === type comparison in mysql?

    I tried the following with my own Family database and got the following result:
    Code:
    mysql> select Add1 from Members where Add1>1 and Add1<100;
    +--------------------+
    | Add1               |
    +--------------------+
    | 17 Powell Building |
    | 43 Padgett Rd      |
    | 41 Padgett Rd      |
    | 43 Padgett Rd      |
    | 4 Birch Court      |
    | 4 Birch Court      |
    | 4 Birch Court      |
    | 4 Birch Court      |
    | 64 Colemand Ln     |
    +--------------------+
    9 rows in set (0.00 sec)
    There are 109 entries in this db.


    hth

    U

  3. #3
    uthus's Avatar
    Senior Member

    Status
    Offline
    Join Date
    Feb 2006
    Location
    Ft Hood
    Posts
    75

    Re: === type comparison in mysql?

    After re-reading the question and my answer, I find that what you want to do is the reverse of a concat(). Looking through my documentation, I can only find that you can do it with REGEXP, but I don't know anything about that language/syntax.

    The file that I found this in is mysql_lang_ref.html . I don't remember where I found it, but have it locally. If you can't find something similar, let me know and I'll pm you the address. I don't want my box getting inundated.

    U

  4. #4
    DeadMeatGF's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    381

    Re: === type comparison in mysql?

    Thanks for the help, Uthus. I suspect that mysql_lang_ref.htm is the downloadable version of http://dev.mysql.com/doc/ but I can't be sure!
    I'll have a look at the REGEXP function too - I have seen some cunning uses for that, but I have no idea how to use it.

    My problem is more like this:
    WHERE column = # returns in the following way:
    If `column` begins with a number (e.g. 17 Powell Building) it returns the number up to the point that it stops being a number - i.e. 17 in this case.
    If `column` is all text, it returns 0
    My problem was that I had a field where a common entry was "00000" - however I couldn't SELECT * WHERE `column` = 0, as that returned all the entries starting with a letter also!
    Luckily, in this case, all the 0 entries are "00000" - not "00", 0, or "000" etc. - so I've ended up testing for "00000" specifically.
    Although this fixes my problem, I'd still be happy to hear of a more elegant solution that works in all cases and distinguishes between a numeric 0 and a 0 result returned due a textual entry.

  5. #5
    uthus's Avatar
    Senior Member

    Status
    Offline
    Join Date
    Feb 2006
    Location
    Ft Hood
    Posts
    75

    Re: === type comparison in mysql?

    Hi DeadMeatGF,

    One more thing. You may be able to get the result, pass it to perl or php to extract the numeric.

    I'll look into that as soon as I can.

    U

  6. #6
    DeadMeatGF's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    381

    Re: === type comparison in mysql?

    I toyed with that idea, but this is at work, and my mySQL server is so much faster than my PHP server that I try and keep as much in the SQL queries as I can

  7. #7
    uthus's Avatar
    Senior Member

    Status
    Offline
    Join Date
    Feb 2006
    Location
    Ft Hood
    Posts
    75

    Re: === type comparison in mysql?

    I looked into the REGEXP thing and it seems to me that when you pass an expression to it, you will only get 1, 0 or NULL. I tried it on my Family db and those were the only results I got.

    U



Tags for this Thread