sqlite replace() function to perform a string replace

Question

I have a column in a sqlite database that I need to modify.

The column is an array of values that looks like this:

0.021460.04419,0.04551,0.02734,0.011,0.005

The array is missing a comma between the first and second value, so the array should look like this:

0.02146,0.04419,0.04551,0.02734,0.011,0.005

The data values will never be greater then 0.9, so I am trying to build an update query that will use the replace() function to perform a string replace, identifying the second "0." and replacing it with ",0.", but I have no idea how to do this.

CL. · Accepted Answer · 2014-08-01 08:44:35Z

6

Just add a comma to all occurrences of 0.:

               replace(TheColumn, '0.', ',0.')

then remove the duplicates:

       replace(replace(TheColumn, '0.', ',0.'), ',,', ',')

and the comma at the beginning:

substr(replace(replace(TheColumn, '0.', ',0.'), ',,', ','), 2)

answered Aug 1, 2014 at 8:44

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bwyss Over a year ago

It almost worked except for the leading comma, using: UPDATE keymap SET key_json = substr(replace(replace(key_json, '0.', ',0.'), ',,', ','), 2); I get: "poe":",0.02731,0.05742,0.05684,0.03973,0.02187"

CL. Over a year ago

Works for me. substr(...,2) always removes one character; what is the first character without the substr?

Bwyss Over a year ago

Ah ok, in that case it is not working for me because I have other stuff in the record: {"latitude":83.1,"longitude":-83,"poe":",0.027310.05742,0.05684,0.03973,0.02187"} so for me it is removing the first curly bracket: "{"

CL. Over a year ago

Then you can replace ",.

Collectives™ on Stack Overflow

sqlite replace() function to perform a string replace

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related