Link to home
Start Free TrialLog in
Avatar of hi4ppl
hi4ppl

asked on

How can I grab this in oracle in SQL

Hi,

here is sqlfidlle

http://sqlfiddle.com/#!4/8089b/1

I would like to get the result below out of that...

fist  |  second
100  | 200
101  | 201
102  | 202
103  | 203
104  | 204
105  | 205

regards
Avatar of Gerwin Jansen
Gerwin Jansen
Flag of Netherlands image

What have you tried already, besides 'select * from ....' ?
Avatar of hi4ppl
hi4ppl

ASKER

Hi,

I did try select regexp_substr(thatfield,'[^,]+]',2,2) as a table1 but it only pick if it will be the start if it's at end it won't pick it ...
select
substr(thatfield,instr(thatfield,'{name') + 6,3) as first,
substr(thatfield,instr(thatfield,'{name') + 10,3) as second
from table1;
SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
will the "field" widths alter in the data? e.g.

THATFIELD
{blahblah,77100,6200}{secondname,200001}
{muchlongerstringinthislocationwithembeddednumbers123name,17801,201234}
{can spaces exist in this location - question to you,1941,51244}


when preparing sample data make sure it covers all situations (well, at least the most likely)