1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Casting of a string to number.Doubt in representation of the number.

Discussion in 'General' started by humanbeing, Jan 4, 2011.

  1. humanbeing

    humanbeing Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,

    I have used a select query in oracle.

    I am extracting a column's data.
    The data are numbers stored in string format.
    I Can not change the datatype of column.

    The column's datatype is varchar and after extracting it i am casting it to NUMBER to use it.

    Compulsory to cast it to number to use it.

    Say i have to extract a number 2.2
    It is stored in database as 2.2 ,but after casting it i get the result as 2,2

    Now i want to get the data with a decimal point in it and the data has to be a number but not a string.

    i.e this is what i want the data to be like 2.2 with datatype number

    And this is what i am getting 2,2 after casting with datatype number


    I have used the function cast() for casting of data from varchar to number.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    Since you chose to not post your CAST() statement one can only presume the NLS settings are suspect as I have not had such issues using CAST() to convert valid numeric strings to numbers. In particular verify that the NLS_NUMERIC_CHARACTERS parameter is set to properly use the . as the decimal. Read here

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch3globenv.htm#i1007759

    to see how to set this parameter and note the example shown displays the values as you now see them with a , as the decimal separator.
     
  3. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Can you post your code and scenario once?
     
  4. humanbeing

    humanbeing Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi SBH.

    Thanks for replying but my problem got solved any way.

    :)
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hi "humanbeing",

    For keeping the spirit of being Club Oracle member, please post how did you solve the issue. It would be helpful for others members who are visiting this page and who are facing the same issue.