1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SQL Oracle how to convert string to Int but also work if no value is there??

Discussion in 'Databases' started by fullylucky, Mar 10, 2020.

  1. #1
    I have an SQL query. One of the columns returns a string.

    But sometimes returns "" nothing.

    but most times it's a number like "2383" but as a string.
    SEMrush
    I need the returned value to be converted from a string to an integer. So from 2383 to 2383.

    I used the old * by 1 trick. However I found when I run the query it errors with those that have "" blanks.

    Is there a better way to convert the string to a number? or another way to handle this?
     
    Solved! View solution.
    fullylucky, Mar 10, 2020 IP
    SEMrush
  2. sarahk

    sarahk iTamer Staff

    Messages:
    26,241
    Likes Received:
    3,867
    Best Answers:
    108
    Trophy Points:
    665
    #2
    oracle has a to_number() function
    have you tried that?
     
    sarahk, Mar 10, 2020 IP
  3. fullylucky

    fullylucky Active Member

    Messages:
    39
    Likes Received:
    7
    Best Answers:
    1
    Trophy Points:
    58
    #3
    Hey Sarah

    I tried to to_number doesnt work. Maybe I got the db type wrong it could be ms SQL server. Not Oracle.
     
    fullylucky, Mar 10, 2020 IP
    JEET likes this.
  4. fullylucky

    fullylucky Active Member

    Messages:
    39
    Likes Received:
    7
    Best Answers:
    1
    Trophy Points:
    58
    #5
    Ok. By the way I did try to_number before even making the original post.

    I will try convert and cast. I haven't used those before much.

    I will try and let you know how it goes.
     
    fullylucky, Mar 10, 2020 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    26,241
    Likes Received:
    3,867
    Best Answers:
    108
    Trophy Points:
    665
    #6
    How do you get the database wrong?
    You don't accidentally end up with either of those two, I might be out of date but those aren't run of the mill databases like MySQL.
     
    sarahk, Mar 10, 2020 IP
  6. fullylucky

    fullylucky Active Member

    Messages:
    39
    Likes Received:
    7
    Best Answers:
    1
    Trophy Points:
    58
    #7
    We recently did a db migration i think so that's how. anyways i'm not very good and not as good as others on this forum. Sorry.

    I have used CAST(string AS INT) and it worked thank you thread close.
     
    fullylucky, Mar 10, 2020 IP
    JEET likes this.
  7. sarahk

    sarahk iTamer Staff

    Messages:
    26,241
    Likes Received:
    3,867
    Best Answers:
    108
    Trophy Points:
    665
    #8
    All fixed so that's good.

    I've had some fun in the past where we've had read access to mainframes and writing reports on them.
     
    sarahk, Mar 10, 2020 IP
    JEET likes this.
  8. JEET

    JEET Notable Member

    Messages:
    3,377
    Likes Received:
    376
    Best Answers:
    16
    Trophy Points:
    235
    #9
    No problem, glad to help :)
     
    JEET, Mar 10, 2020 IP