CODEWITHSUNDEEP
oracle9i
SANJAY
SANJAY

Reputation: 9

How to split the String in Oracle 9i

Can any body help me out to solve this issue, I need to split the String using oracle 9i
String exp=" Employee Number * Pay + Years of Experience"
My Expected output is after parsing "Employee Number" "Pay" "Years of Experience".
Is it possible to do in oracle 9i Query, Thanks a ton in advance

Upvotes: 0

Views: 857

Answers (3)

sugikutti
sugikutti

Reputation: 21

select substr('Employee * Pay + Years of experience',1,instr('Employee * Pay + Years of experience','*')-2),substr('Employee * Pay + Years of experience',instr('Employee * Pay + Years of experience','*')+1,length('Employee * Pay + Years of experience')-instr('Employee * Pay + Years of experience','*')-21),substr('Employee * Pay + Years of experience',instr('Employee * Pay + Years of experience','+')+1) from dual;

Upvotes: 2

sugikutti
sugikutti

Reputation: 21

select substr('Employee * Pay + Years of experience', 1, instr('Employee * Pay + Years of experiece', '*') - 2),substr('Employee * Pay + Years of experience',instr('Employee * Pay + Years of experiece', '*') + 2,instr('Employee * Pay + Years of experience', '+') - instr('Employee * Pay + Years of experiece','*')-3),substr('Employee * Pay + Years of experience', instr('Employee * Pay + Years of experience', '+') + 2)from dual;

Upvotes: 0

Amir Syafrudin
Amir Syafrudin

Reputation: 853

A straight-forward answer would be to use SUBSTR and INSTR function. Here's an example:

select substr('Employee * Pay + Years', 1, instr('Employee * Pay + Years', '*') - 2),
        substr('Employee * Pay + Years', instr('Employee * Pay + Years', '*') + 2, 
                    instr('Employee * Pay + Years', '+') - instr('Employee * Pay + Years', '*') - 3),
        substr('Employee * Pay + Years', instr('Employee * Pay + Years', '+') + 2)
from dual

Work your way to what you need from there. :)

Upvotes: 1

Related Questions