Replacing an string on a field via MySQL

Question

I have the following field (which is a string) of one of my tables:

'[{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
  {"id":"7","value":"Reuters"},
  {"id":"20","value":"2017/03/17"}]'`

As you can see, the last part, the one with id=20 has a date on it, 2017/03/17.

Can someone tell me how can I update that field so I can get the output 2017-03-17?

My desired output after the update would be:

'[{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
 {"id":"7","value":"Reuters"},
 {"id":"20","value":"2017-03-17"}]'`

If I use:

UPDATE mytable
SET myfield = replace(myfield , '/', '-');

It removes the / of the links, and I want to mantain them.

PS: The ids of the fields are always the same (the one for the date is always 20 for example) the only thing that changes are the values.

Then why you dont use substring operation? Search for `"{id":"20"` and then for closing `}` and fix just that substring — Juan Carlos Oropeza, Mar 29 '17 at 13:49
How can I search `"{id":"20"` with `SUBSTRING`? As long as I know for using `SUBSTRING` you need to know the position, and this varies on my fields. — Avión, Mar 29 '17 at 13:57
Possible duplicate of [Parse JSON in MySQL](http://stackoverflow.com/questions/26770051/parse-json-in-mysql) — Jay Blanchard, Apr 03 '17 at 13:52

score 0 · Answer 1 · edited May 23 '17 at 12:25

Maybe there is a better way, But you can :

split the string into rows using { as separator
find the row with "ID=20"
do the replace
join all rows back together using GROUP_CONCAT

DEMO

SELECT  
  SUBSTRING_INDEX(SUBSTRING_INDEX(T.json, '{', numbers.n), '{', -1) name,

  CASE WHEN INSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(T.json, '{', numbers.n), '{', -1), '"id":"20"') > 0 
       THEN REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(T.json, '{', numbers.n), '{', -1), '/', '-')
       ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(T.json, '{', numbers.n), '{', -1)  
  END as result

FROM 
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers 
JOIN (
        SELECT '[{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
          {"id":"7","value":"Reuters"},
          {"id":"20","value":"2017/03/17"}]' as json
    ) T
  on CHAR_LENGTH(T.json)
     -CHAR_LENGTH(REPLACE(T.json, '{', ''))>=numbers.n-1
order by
  n

OUTPUT

OPTION 2

SELECT SUBSTRING(json, 1, pos_begin - 1 ) as side_left,
       REPLACE(SUBSTRING(json, pos_begin, pos_end - pos_begin + 1), '/', '-') as side_middle,
       SUBSTRING(json, pos_end + 1, LENGTH(json)) as side_end       

FROM (
        SELECT LOCATE('{"id":"20"', json) as pos_begin, 
               LOCATE('}', json, LOCATE('{"id":"20"', json)) as pos_end,
               T.json        
        FROM   (
                SELECT '[{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
                  {"id":"7","value":"Reuters"},
                  {"id":"20","value":"2017/03/17"}]' as json
            ) T
    ) S
;

OUTPUT

score 0 · Accepted Answer · answered Mar 29 '17 at 14:39

you can do it with a query like this:

UPDATE  mytable
    SET myfield = CONCAT (
        SUBSTRING_INDEX(myfield, '"id":"20","value":', 1)
        , '"id":"20","value":',
        , REPLACE (SUBSTRING_INDEX(myfield, '"id":"20","value":', -1),'/','-')
    )
WHERE ....:

sample

mysql> SELECT
    ->     CONCAT (
    ->     SUBSTRING_INDEX('[{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
    '>  {"id":"7","value":"Reuters"},
    '>  {"id":"20","value":"2017/03/17"}]', '"id":"20","value":', 1),
    ->  '"id":"20","value":',
    ->  REPLACE (SUBSTRING_INDEX('[{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
    '>  {"id":"7","value":"Reuters"},
    '>  {"id":"20","value":"2017/03/17"}]', '"id":"20","value":', -1),'/','-')
    ->  ) AS result;
+------------------------------------------------------------------------------------------------------------------------------------------------+
| result                                                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"id":"6","value":["http://www.google.com","http://www.google.com","new"]},
 {"id":"7","value":"Reuters"},
 {"id":"20","value":"2017-03-17"}] |
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql>

I knew there was an easy way, but I'm still trying to understand this. I feel this assume the `"id:20"` is the last one? Would work if there is `"id:50 http://www.sample.com"` ? — Juan Carlos Oropeza, Mar 29 '17 at 15:25
I split the string in two piece at "id:20" and change the / to - . after this i concat the first+"id:20"+replaced strin. so you can change it to everything — Bernd Buffen, Mar 29 '17 at 15:29
As I say you assume the `ID:20` is the last element. Not sure if that will always true. Check this [**demo**](http://rextester.com/CPFGZ67255) — Juan Carlos Oropeza, Mar 29 '17 at 15:43

Replacing an string on a field via MySQL

2 Answers2