-
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
-
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
-
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.
-
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
-
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;
-
Originally Posted by aryasen
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.
-
Originally Posted by PAVB
It returns "123456" so... it doesn't work.
You have to test!!!
This is first question answer.
-
Originally Posted by aryasen
This is first question answer.
you still have to test and not give crap answers, worse than no answer at all
-
Originally Posted by davey23uk
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|