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

HTML Code:

---

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

---

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

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;

Quote:

---

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

Quote:

---

Originally Posted by PAVB

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

You have to test!!! :)

---

This is first question answer. :)

Quote:

---

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

Quote:

---

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