In a string, replace third number in each set with new number

Question

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?

• alexgibbs: 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

Answer 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,})([,)])', ' 12円', 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.