update

[prodadmin]

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

[prodadmin]

i got the answer for 1st one using replace

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

thanks

[PAVB]

select  substr('12345-6',1,(instr('12345-6','-')-1)) from dual;

[prodadmin]

thank you and your help is much appreciated and congratulations on publishing book

[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;

[PAVB]

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!!!

[aryasen]

It returns "123456" so... it doesn't work.

You have to test!!!

This is first question answer.

[davey23uk]

This is first question answer.

you still have to test and not give crap answers, worse than no answer at all

[aryasen]

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