SQL gurus plz help!

Discussion in 'OT Technology' started by jasuklee, Jun 23, 2003.

  1. jasuklee

    jasuklee New Member

    Joined:
    Jun 5, 2003
    Messages:
    1,491
    Likes Received:
    0
    Location:
    T.dot / H-town
    gotta convert a string that would be like this

    $99.00

    into an int like this

    99

    how do you do that??
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    SQL server? MySQL? Access? Oracle? what platform? and is the $99.00 in a string format or currency or what?
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Try forcing it without any modifications - I know access doesn't care.
     
  4. EPD Hater

    EPD Hater New Member

    Joined:
    Aug 26, 2001
    Messages:
    538
    Likes Received:
    0
    you can't physically convert, but in your SELECT clause, you can perform a scalar function against it. But yeah, answer SLED, then we can find out exactly how that value is stored.
     
  5. Scoob_13

    Scoob_13 Anything is possible, but the odds are astronomica

    Joined:
    Oct 5, 2001
    Messages:
    73,787
    Likes Received:
    38
    Location:
    Fort Worth. Hooray cowgirls.
    Well, I'd personally strip the $ off the front and do atoi if it were in C++ or C# (I believe C# has the same command?)

    Depends really on how you're using it.
     
  6. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    I think we'd all like to buy a clue...

    You inserting or pulling this out as a select? What flavor database you working with?

    Assuming its a string and you're trying to trim it down to in int via MySQL SELECT, you'll need to read up on SUBSTRING and LOCATE. You'll need to do a substring to eliminate the '$', and then one more to grab the left half of the decimal (use LOCATE to figure out where in the string is the '.', then your substring can lock on to that and return you the first chunk). For MySQL string functions: http://www.mysql.com/doc/en/String_functions.html

    Your mileage may vary between various DB engines. You could also use regular expressions from within MySQL if I remember correctly (REGEX() - powerful and with the right regular expression could pull out what you need, although you may take a small performance hit for it)

    Typically, DB engines are not all that quick when it comes to string fiddling and I'd recommend formating the string via your outside code (be it C/C++/Java/ASP/PHP/etc).
     
  7. EPD Hater

    EPD Hater New Member

    Joined:
    Aug 26, 2001
    Messages:
    538
    Likes Received:
    0
    SUBSTR and LOCATE functions also work well when you want to rearrange a persons name if the format is LAST FIRST and you want to show as FIRST LAST
     
  8. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I think the whole point is it depends on the database engine being used. And chances are, this guy has long since moved on from the problem!
     
  9. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    :eek3:
     
  10. uhhhh ... you could CAST or CONVERT (I'm not sure which offhand) in MSSQL ...
     
  11. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    both work in mssql actually
     
  12. yes but to achieve the proper datatype AND formatting, would they both still work?
     
  13. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    the only difference between the two functions is that convert() has different date formatting.... i believe
     

Share This Page