CODEWITHSUNDEEP
sqloracle-database
Deva Dwi
Deva Dwi

Reputation: 1

How to split a value by capital letter

I have a table like this

JobNo |  Properties
--------------------------------------------------------------------------------------------
1     |  Active=nDaysOfWork=mon,tue,wed,thu,fri,sat,sunStartTime=08:00EndTime=09:00
2     |  Active=nDaysOfWork=sat,sunStartTime=12:00EndTime=13:00

I want to split the Properties value

Note: The header(Active, DaysOfWork, StartTime, EndTime) can be used to be a parameter

and this is what I want

JobNo   |  Active   |   DaysOfWork                     |   StartTime       |     EndTime
----------------------------------------------------------------------------------------
1       |     n     |   mon,tue,wed,thu,fri,sat,sun    |    08:00          |      09:00
2       |     y     |   sat,sun                        |    12:00          |      13:00

Upvotes: 0

Views: 76

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21115

Well column names can't be parameters of the query, so you will have to somehow intergrate them in the query.

Here is one possibility - in the first step you get the position of the parameters using instr, in the next step you scrape the values with considering the parameter name length.

with prop as (
select 
jobNo,
Properties,
instr(Properties,'Active=') col1,
instr(Properties,'DaysOfWork=') col2,
instr(Properties,'StartTime=') col3,
instr(Properties,'EndTime=')  col4 
from my_tab)
select jobNo,
substr(Properties, col1+7, col2 - col1 -7) as Active,
substr(Properties, col2+11, col3 -col2 -11) as DaysOfWork,
substr(Properties, col3+10, col4 - col3 -10 ) as StartTime,
substr(Properties, col4+8) as EndTime
from prop;

JOBNO, ACTIVE, DAYSOFWORK,                  STARTTIME, ENDTIME
1      n       mon,tue,wed,thu,fri,sat,sun  08:00      09:00
2      n       sat,sun                      12:00      13:00

You 'll have to extend the logic if the order of the paramater may change.

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

You can use regular expressions. Here is an example with REGEXP_REPLACE:

select
  jobno,
  regexp_replace(properties, '^Active=(.).*$', '\1') as active,
  regexp_replace(properties, '^Active=.DaysOfWork=(.*)StartTime=.*$', '\1') as daysofwork,
  regexp_replace(properties, '^.*StartTime=(.*)EndTime.*$', '\1') as starttime,
  regexp_replace(properties, '^.*EndTime=(.*)$', '\1') as endtime
from mytable;

Upvotes: 1

Related Questions