Home » SQL & PL/SQL » SQL & PL/SQL » how to separate mobileno and landlineno in oracle(input is in single field) (xp,oracle)
icon14.gif  how to separate mobileno and landlineno in oracle(input is in single field) [message #409192] Sat, 20 June 2009 01:51 Go to next message
arun.dwh
Messages: 1
Registered: June 2009
Junior Member
I have text file like this
(INPUT)

phone_no
9942242132*04423659874*04478942516*9942242358*9987 456224
9944542132*942362358*04478918516*04423869874*99874 58224*04456987214

I need OUT PUT like this( out put is in oracle)
mobileno,landlineno
9942242132,04423659874
9942242358,04478942516
9987456224,04423869874
9944542132,04478918516
9987458224,04456987214
942362358
Re: how to separate mobileno and landlineno in oracle(input is in single field) [message #409193 is a reply to message #409192] Sat, 20 June 2009 01:57 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use SUBSTR and INSTR functions.

If you are in 10g and up you can also use REGEXP_SUBSTR.

Before your next question, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

And post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Previous Topic: Help with SQL
Next Topic: Call multiple schema in Function
Goto Forum:
  


Current Time: Sat Feb 08 07:50:43 CST 2025