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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply