1

I have a string that has coordinates.

  • Individual coordinates are separated by spaces.
  • Vertices (X Y Z coordinates) are separated by commas.
  • Vertex groups are wrapped in brackets and separated by commas.
Before:
MULTILINESTRING M (( 0.0 5.0 123, 10.0 10.0 456, 30.0 0.0 789),( 50.0 10.0 -123, 60.0 10.0 -100000.0))

I want to replace the third coordinate in each vertex with a new number:

After:
MULTILINESTRING M (( 0.0 5.0 1, 10.0 10.0 1, 30.0 0.0 1),( 50.0 10.0 1, 60.0 10.0 1))

For simplicity, we can use the number 1 as the replacement number.


What's a good way to replace those numbers in that string?

1
  • I put up one possible solution, but wondered if this question might belong better over on SO instead, maybe you can get more/other solutions if this is migrated there. Thanks
    – alexgibbs
    Apr 2, 2022 at 0:24

1 Answer 1

1

If the structure of the vertices and vertex groups is uniform, and the goal is indeed to replace all third coordinates with the same number literal, this can be accomplished with an ordinary regexp_replace.

Presuming the numbers are all using decimal characters, here's an example with the text you provided:

SELECT REGEXP_REPLACE('MULTILINESTRING M (( 0.0 5.0 123, 10.0 10.0 456, 30.0 0.0 789),( 50.0 10.0 -123, 60.0 10.0 -100000.0))'
         , '[ ]([-+0-9.eE]{1,})([,)])', ' 1\2', 1, 0) AS REPLACED_STRING
FROM DUAL;

Result:

MULTILINESTRING M (( 0.0 5.0 1, 10.0 10.0 1, 30.0 0.0 1),( 50.0 10.0 1, 60.0 10.0 1))

The above will match any ordinary decimal number preceded by a space and preceding a comma or closing parens, and replace it with 1.

If the replacement instead needs to be derived from the captured coordinate , it may be worth considering extracting the coordinates and manipulating them before rebuilding the string, depending on the nature of the derivation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.