Skip to Main Content
  • Questions
  • returning text from between specific characters

Breadcrumb

May 4th

Question and Answer

Chris Saxon

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

Comments

Jonathan, March 01, 2017 - 10:35 am UTC

Thank you I got there in the end, the explanation of how the functions worked was most useful.
Though I think the logic in the suggested formula is a little wrong.

SUBSTR(STR,INSTR(STR,'BY')+3,(INSTR(STR,'JOBTITLE')-INSTR(STR,'BY')-3))

the last part I had to make a +Number rather than a -3
Chris Saxon
March 01, 2017 - 2:34 pm UTC

Thanks! We're here to teach you the concepts. We expect you'll have to adapt the examples slightly to meet your requirements