DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: update

  1. #1
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    update

    hello guys ,

    I have values in a column like this " 1234-5" or "12345-6"

    and have the following questions

    1.how do we change that to 12345/123456 or remove the dash in all values of that column
    2.how do we change all values to this 1234 or 12345 (remove anything after -)

    not sure which function I can use

    any help is appreciated

    thanks

  2. #2
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    i got the answer for 1st one using replace

    not sure about 2nd query ,any help is much appreciated

    thanks
    Last edited by prodadmin; 03-02-2010 at 01:30 PM. Reason: edit

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    HTML Code:
    select  substr('12345-6',1,(instr('12345-6','-')-1)) from dual;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    thank you and your help is much appreciated and congratulations on publishing book
    Last edited by prodadmin; 03-02-2010 at 04:03 PM. Reason: edit

  5. #5
    Join Date
    Oct 2009
    Location
    Banglore
    Posts
    8
    select to_number( substr('12345-6',1,(instr('12345-6','-')-1)) ||substr('12345-6',(instr('12345-6','-')+1),( length('12345-6')-instr('12345-6','-')) ) )
    from dual;

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by aryasen View Post
    select to_number( substr('12345-6',1,(instr('12345-6','-')-1)) ||substr('12345-6',(instr('12345-6','-')+1),( length('12345-6')-instr('12345-6','-')) ) ) from dual;
    It returns "123456" so... it doesn't work.

    You have to test!!!
    Last edited by PAVB; 03-04-2010 at 05:50 PM. Reason: Typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Oct 2009
    Location
    Banglore
    Posts
    8
    Quote Originally Posted by PAVB View Post
    It returns "123456" so... it doesn't work.

    You have to test!!!
    This is first question answer.

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Quote Originally Posted by aryasen View Post
    This is first question answer.
    you still have to test and not give crap answers, worse than no answer at all

  9. #9
    Join Date
    Oct 2009
    Location
    Banglore
    Posts
    8
    Quote Originally Posted by davey23uk View Post
    you still have to test and not give crap answers, worse than no answer at all
    hello,

    I tested properly Can you tell what is wrong in that?


    SQL> select to_number( substr('12345-6',1,(instr('12345-6','-')-1)) ||substr('12345-6',(instr('12345
    -6','-')+1),( length('12345-6')-instr('12345-6','-')) ) )
    2 from dual;

    TO_NUMBER(SUBSTR('12345-6',1,(INSTR('12345-6','-')-1))||SUBSTR('12345-6',(INSTR(
    --------------------------------------------------------------------------------
    123456

    SQL> select to_number(substr('12345-6',1,(instr('12345-6','-')-1)))
    2 from dual;

    TO_NUMBER(SUBSTR('12345-6',1,(INSTR('12345-6','-')-1)))
    -------------------------------------------------------
    12345

Posting Permissions

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


Click Here to Expand Forum to Full Width