How to split a string in multiple places in Oracle SQL

Question

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?

Probably connected https://stackoverflow.com/questions/3710589/is-there-a-function-to-split-a-string-in-pl-sql — Gonzalo Odiard, Jul 19 '22 at 12:10
@VBoka yes its always that format "POINT" then a space then an open bracket. — Rob Morris, Jul 19 '22 at 12:43
@WeDBA the split changes for the second value 52.123456 on every record. — Rob Morris, Jul 19 '22 at 12:56
So on some record it will be POINT (-2.342 52.3084597) then on the next record it will be POINT (-2.3094092 52.13493) — Rob Morris, Jul 19 '22 at 12:57

score 0 · Answer 1 · answered Jul 19 '22 at 13:02

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

Hello @RobMorris , has this answer helped you? – VBoka Jul 26 '22 at 06:54 — VBoka, Jul 26 '22 at 06:54

score -1 · Answer 2 · answered Jul 19 '22 at 12:43

-1

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

answered Jul 19 '22 at 12:43

WeDBA

343
4
7

How to split a string in multiple places in Oracle SQL

2 Answers2