How to separate parts of a text

user_19504234 0
Viewed 16

I need help!

I have a field and I need to separate it into 3 parts in sql.

For example, I have the following result: HidroCHLOROTHiazide 25mg comp - Dose: 25 Tablets - Interval: 1x - once a day - Via: Oral

The result that I need is:

Column 1: HydroCHLOROTHiazide 25mg comp (This field goes from start to '- Dose:')

Column 2: 25 Pills (This field always starts after '- Dose:' and before '- Interval:')

Column 3: 1x - once a day (This field always starts after '- Interval:' and before '- Via:')

But each row of my table comes with different sizes.

Another example : desogestrel 0.075mg comp rev - Dose: .75 Tablet - Interval: 1x - once a day - Via: Oral

postgresql
1 Answers

Use regexp_replace() for this:

with invars as (
  select 'HidroCHLOROTHiazide 25mg comp - Dose: 25 Tablets - Interval: 1x - once a day - Via: Oral' as scrip
)
select regexp_replace(scrip, '^(.*) - Dose:.*', '\1') as med,
       regexp_replace(scrip, '.* - Dose: (.*) - Interval:.*', '\1') as dose,
       regexp_replace(scrip, '.* - Interval: (.*) - Via:.*', '\1') as interval
  from invars;
┌───────────────────────────────┬────────────┬─────────────────┐
│              med              │    dose    │    interval     │
├───────────────────────────────┼────────────┼─────────────────┤
│ HidroCHLOROTHiazide 25mg comp │ 25 Tablets │ 1x - once a day │
└───────────────────────────────┴────────────┴─────────────────┘
(1 row)
user_138083190
Related