CODEWITHSUNDEEP
mysqlsqlstringreplacesubstring
user2976138
user2976138

Reputation: 85

MYSQL Query to replace a section of a string

I have a ton of positions saved in a database in the following format

Examples: 

[32.306,[7195.4,9414.24,0.005]]
[219.184,[7197.41,9416.66,-0.003]]
[161.215,[1170.26,4852.79,3.815e-04]]
[37.338,[479.163,3757.15,-0.005]]
[11.719,[12436.5,4780.36,-9.46e-04]]

The coordinates are in the format [DIRECTION,[X,Y,Z]]

I would like to replace all of the Z coordinates with 0. Been struggling with finding the correct way of doing this in an SQL query.

Upvotes: 0

Views: 262

Answers (2)

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

CONCAT(SUBSTRING_INDEX(col,',',3), ',0]]') col

Result:

|                           COL |
|-------------------------------|
|   [32.306,[7195.4,9414.24,0]] |
| [219.184,[7197.41,9416.66,0]] |
| [161.215,[1170.26,4852.79,0]] |
|  [37.338,[479.163,3757.15,0]] |
|  [11.719,[12436.5,4780.36,0]] |

Upvotes: 1

user2970362
user2970362

Reputation: 384

I would chop up the your string field using SUBSTRING and cast the values to floats and store them in 4 different fields (direction, x, y and z). Then you can easily update the Z value (and any other value)

When you need the more complex string representation, just concatenate the 4 fields by casting it back to varchar.

Upvotes: 1

Related Questions