Thanks for the question, Jonathan.
Asked: February 28, 2017 - 10:42 am UTC
Last updated: March 01, 2017 - 2:34 pm UTC
Version: Version 4.0.3.16
Viewed 1000+ times
You Asked
I have some text in a column stored like below. with time/date stamps, worker names, position and team in a long string.
"Verified at HH:MMPM on DD/MM/YYYY by FirstName Surname Job Title: Worker, Organisation: Area 1 Team"
I would like to return the data split into separate columns
one for the time, the part between the 'AT and 'ON' eg, HH:MMpm
one for the date, the part between the 'ON and 'by' eg, DD/MM/YYYY
one for the name, the part between the 'by' and 'job title' eg, FirstName Surname
one for the job title, the part between the 'job title' and ', organisation' eg, Worker
and one for the organisation, the part after ', organisation:' eg, Area 1 Team
I did the first parts using expressions like this
substr(text_answer,12,9) Verfied_time,
substr(text_answer,24,10) Verfied_date,
but I am not sure how to handle the later parts.
Thank you for your assistance
and Chris said...
Instr gives the location of a particular piece of text in a string. So you can combine this with substr to do the kind of thing you're looking for:
- Substr's second argument is the starting point. So pass the result of instr for your starting point + the character offset after this
- The third is the length. So subtract your instr from the step above from the result of instr on the string marking the end. Also subtract the offset of extra characters
For example:
with rws as (
select 'Verified at HH:MMPM on DD/MM/YYYY by FirstName Surname Job Title: Worker, Organisation: Area 1 Team' str from dual
)
select instr(str, 'by'),
instr(str, 'Job Title'),
substr(str, instr(str, 'by')+3, (instr(str, 'Job Title')-instr(str, 'by')-3))
from rws;
INSTR(STR,'BY') INSTR(STR,'JOBTITLE') SUBSTR(STR,INSTR(STR,'BY')+3,(INSTR(STR,'JOBTITLE')-INSTR(STR,'BY')-3))
35 56 FirstName Surname
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment