SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2002
    Location
    Madison, WI
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query with decimal range on text column

    Hello,

    I have a column in a table with field type text. However, it contains decimal values like 0.312, 0.445, etc.

    I am writing a pretty standard query to get date from my table, but am unsure how I can query decimal ranges given that the field type is text. A sample query:

    Code:
    SELECT * 
    FROM myTable 
    WHERE myfield(BETWEEN 0.350 AND 0.450)
    Do I need to do some kind of math function here?

    Please advise, thanks.
    :::::::::::::::::::::::::::::::::::::::::::::::::::
    Versa Studio
    ExpressionEngine experts
    bas (at) versa studio dot com

  2. #2
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    Belgian in Mexico
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It should work fine if you drop the () from your WHERE clause.

    But I have to ask: Why do you store decimal values in a text field?

    Michaël
    MichaŽl Niessen
    http://assemblysys.com
    (Countries/states/cities with latitude & longitude,
    weathercodes & topical databases)

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2002
    Location
    Madison, WI
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Michael,

    Thanks. I'm storing decimal vals in a text field within a CMS that automatically creates these fields. I could change the field type, but I'd prefer not to.

    The second AND clause is conditional, so I thought I had to have the parentheses?

    Code:
    SELECT *
    FROM myTable
    WHERE something = 'blat'
    AND something else = 'something'
    AND(myfield BETWEEN .450 and .550)
    AND bork = 'anotherthing'
    :::::::::::::::::::::::::::::::::::::::::::::::::::
    Versa Studio
    ExpressionEngine experts
    bas (at) versa studio dot com

  4. #4
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    Belgian in Mexico
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT *
    FROM myTable
    WHERE something = 'blat'
    AND something else = 'something'
    AND(myfield BETWEEN .450 and .550)
    AND bork = 'anotherthing'
    The parentheses do absolutely nothing here.
    Quote Originally Posted by 4midori View Post
    The second AND clause is conditional
    I don't understand what you mean. Could you explain a little more?

    MichaŽl
    MichaŽl Niessen
    http://assemblysys.com
    (Countries/states/cities with latitude & longitude,
    weathercodes & topical databases)

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2002
    Location
    Madison, WI
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gracias, MichaŽl. Pasa que poniendo mŠs atenciůn a los queries, saliů bien.

    -Ben
    :::::::::::::::::::::::::::::::::::::::::::::::::::
    Versa Studio
    ExpressionEngine experts
    bas (at) versa studio dot com

  6. #6
    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)
    you need to use CAST() to convert the field before you do the comparison:
    Code:
    cast(myfield as decimal) between 0.450 and 0.550

  7. #7
    SitePoint Zealot
    Join Date
    Oct 2002
    Location
    Madison, WI
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Longneck,

    It looks like it works without that. I have decimal values stored in the DB.

    Is the CAST function required?

    Thx.
    :::::::::::::::::::::::::::::::::::::::::::::::::::
    Versa Studio
    ExpressionEngine experts
    bas (at) versa studio dot com

  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)
    in mysql? maybe not. but it would be in any other RDBMS.


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
  •