Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: using sub string

    Hie all
    I am facing some difficulties here

    I have this data in two columns in a table

    new_balance, reload_amount
    lance = 983,amount=1000
    lance = 1002,amount=1000
    ance = 1095,amount=1000

    i need to transfer this to another table by just taking the digits only and adding two decimal places, can someone explain to me how

    it should look like this
    new_balance, reload_amount
    9.83,10.00
    10.02,10.00
    10.95,10.00

    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The easiest way would be using simple dividing by 100: for example, 983 / 100 = 9.83, or 1095 / 100 = 10.95, etc.

    There is no need (unless you have a really good reason) to store numeric values formatted as strings - use NUMBER datatype to store numeric values, and format them in the reporting tool (SQL*Plus, Report Developer, ...).

    If you insist on this, use TO_CHAR function and format the output, such as

    SELECT TO_CHAR(new_balance / 100, '999G990D00) FROM your_table;

  3. #3
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    Little foot

    Okie about the decimal point
    but then how to remove the unwanted things such as the alphabets bolded

    ance = 983,amount=1000


    ??

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Gosh! It never crossed my mind that "ance = 983" and "amount=1000" are column values; I expected 983 and 1000 only ...

    In that case, I'd suggest you to search for "=" sign and select the rest of the string, such as

    SELECT TO_NUMBER(SUBSTR(new_balance, INSTR(new_balance, '=') + 1)) / 100 FROM your_table;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •