Hi Guys is it possible to use regexp_split_to_table to split my column on 2 different levels. AS in split by ';' and then split by ' '. I have this "-29.617356578819454 30.394328366965055 0.0 0.0;-29.61735976394266 30.39434471167624 0.0 0.0;" a column called "RPT_FTR_TRACE". ; seperates each coordinates and a space seperate long from lat. I want regexp_split_to_table to split it for me so my final query have "-29.617356578819454 30.394328366965055 0.0 0.0; in one column and long=-29.617356578819454 column and lat= 30.394328366965055 column

WITH a AS ( SELECT DISTINCT "_URI", "_SUBMISSION_DATE","INFO_TEAM_NAME","INFO_SUPERVISOR_NAME","INFO_LOCATION","RPT_FTR_WIDTH", "RPT_FTR_TRACE" AS t FROM odk_duct.duct_monitoring_core )/* selection from odk_duct.duct_monitoring_core table,*/ ,b AS ( SELECT a."_URI", a."_SUBMISSION_DATE",initcap(a."INFO_TEAM_NAME"),"RPT_FTR_TRACE", b.p, b.id

       FROM odk_duct.duct_monitoring_core a,
       LATERAL regexp_split_to_table("RPT_FTR_TRACE", ';'::text) WITH ORDINALITY b(p, id)
       WHERE b.p <> ''::text
        ) select * from b

Hi Guys is it possible to use regexp_split_to_table to split my column on 2 different levels. AS in split by ';' and then split by ' '. I have this "-29.617356578819454 30.394328366965055 0.0 0.0;-29.61735976394266 30.39434471167624 0.0 0.0;" a column called "RPT_FTR_TRACE". ; seperates each coordinates and a space seperate long from lat. I want regexp_split_to_table to split it for me so my final query have "-29.617356578819454 30.394328366965055 0.0 0.0; in one column and long=-29.617356578819454 column and lat= 30.394328366965055 column

WITH a AS (
         SELECT DISTINCT "_URI",
            "_SUBMISSION_DATE","INFO_TEAM_NAME","INFO_SUPERVISOR_NAME","INFO_LOCATION","RPT_FTR_WIDTH",
            "RPT_FTR_TRACE" AS t
           FROM odk_duct.duct_monitoring_core
        )/* selection from odk_duct.duct_monitoring_core table,*/ 
    ,b AS (
    SELECT a."_URI",
           a."_SUBMISSION_DATE",initcap(a."INFO_TEAM_NAME"),"RPT_FTR_TRACE",
           b.p,
           b.id
          
           FROM odk_duct.duct_monitoring_core a,
           LATERAL regexp_split_to_table("RPT_FTR_TRACE", ';'::text) WITH ORDINALITY b(p, id)
           WHERE b.p <> ''::text
            ) select * from b

Hi Guys is it possible to use regexp_split_to_table to split my column on 2 different levels. AS in split by ';' and then split by ' '. I have this "-29.617356578819454 30.394328366965055 0.0 0.0;-29.61735976394266 30.39434471167624 0.0 0.0;" a column called "RPT_FTR_TRACE". ; seperates each coordinates and a space seperate long from lat. I want regexp_split_to_table to split it for me so my final query have "-29.617356578819454 30.394328366965055 0.0 0.0; in one column and long=-29.617356578819454 column and lat= 30.394328366965055 column

Hi Guys is it possible to use regexp_split_to_table to split my column on 2 different levels. AS in split by ';' and then split by ' '. I have this "-29.617356578819454 30.394328366965055 0.0 0.0;-29.61735976394266 30.39434471167624 0.0 0.0;" a column called "RPT_FTR_TRACE". ; seperates each coordinates and a space seperate long from lat. I want regexp_split_to_table to split it for me so my final query have "-29.617356578819454 30.394328366965055 0.0 0.0; in one column and long=-29.617356578819454 column and lat= 30.394328366965055 column

WITH a AS ( SELECT DISTINCT "_URI", "_SUBMISSION_DATE","INFO_TEAM_NAME","INFO_SUPERVISOR_NAME","INFO_LOCATION","RPT_FTR_WIDTH", "RPT_FTR_TRACE" AS t FROM odk_duct.duct_monitoring_core )/* selection from odk_duct.duct_monitoring_core table,*/ ,b AS ( SELECT a."_URI", a."_SUBMISSION_DATE",initcap(a."INFO_TEAM_NAME"),"RPT_FTR_TRACE", b.p, b.id

       FROM odk_duct.duct_monitoring_core a,
       LATERAL regexp_split_to_table("RPT_FTR_TRACE", ';'::text) WITH ORDINALITY b(p, id)
       WHERE b.p <> ''::text
        ) select * from b