How to split a string in multiple places in Oracle SQL-CodePudding

I have some sql that brings back information about the geometry of jobs.

very simply this is

select
job.job_number,
job.WKT_values
from
job

the job.WKT_values field brings back information about the location of the job (latitude and longitude) in the following format

POINT (-2.534981 52.14983)

what i would like to is add some code to the report so that the -2.534981 appears in one column and 52.14983 appears in another column and I can then pull these straight into power bi for mapping.

how do i go about editing the sql so that it splits the field based on a "(" for the first split and then a space for the second split?

CodePudding user response：

Use instr() to find out the position of the splitters, then use substr() extract the data.

CodePudding user response：

I realize now you need the results in separate columns like this:

with cte as (select replace(replace(WKT_values, 'POINT (', ''), ')', '') as WKT_values
             from job)
select SUBSTR(WKT_values, 1, INSTR(WKT_values, ' ')-1) AS one_column,
       SUBSTR(WKT_values, INSTR(WKT_values, ' ') 1) AS another_column
from cte

At first I thought you need them in the same row:

with cte as (select replace(replace(WKT_values, 'POINT (', ''), ')', '') as WKT_values
             from job)
select trim(regexp_substr(WKT_values, '[^ ] ', 1, levels.column_value))  
from cte, 
     table(cast(multiset(select level 
                         from dual 
                         connect by level <= length (regexp_replace(cte.WKT_values, '[^ ] '))    1) as sys.OdciNumberList)) levels

DEMO