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

by

The 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?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 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.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment