MySQL: Select specific strings and replace on several locations

Question

The table have several columns. One of them is 'video'. There are many rows, all cells contain one string. The string should get more accessories. They contain different names for the label and the url, expect the 'XYREE -' inside the label, which never changes.

This is the wanted string:

{"label":"XYREE - A small title",
"link":"http://google.com/hasAlengthOf50symbols"}

This is the actual string:

XYREE - A small title |-|http://google.com/hasAlengthOf50symbols

This was my try to find the wanted rows and to replace a part of it.

SELECT *  FROM `items` WHERE `video` 
NOT REGEXP '{"label":"XYREE' REPLACE(video, 'XYREE', '{"label":"XYREE')

Thanks in advance.

You want to update table , replace ACTUAL string With WANTED string in column video . — Mudassir Hasan
– Mudassir Hasan, Commented Jan 5, 2014 at 3:05
It's still very unclear what you actually want. Please 1) State clearly do you want to change values in the table or you just want to select augmented values 2) provide a few rows of sample data with dissimilar values in a tabular form 3) provide the desired outcome again in a tabular form — peterm
– peterm, Commented Jan 5, 2014 at 3:23

peterm · Accepted Answer · 2014-01-05 03:11:14Z

Are you looking for something like this?

SELECT id, CONCAT('{"label":"', 
                  TRIM(SUBSTRING_INDEX(video, '|-|', 1)), 
                  '","link:"', 
                  TRIM(SUBSTRING_INDEX(video, '|-|', -1)), '"}') video
  FROM items
 WHERE ...

Sample output:

| ID |                                                                             VIDEO |
|----|-----------------------------------------------------------------------------------|
|  1 | {"label":"XYREE - A small title","link:"http://google.com/hasAlengthOf50symbols"} |

Here is SQLFiddle demo

If you need to actually replace (update) values in your table

UPDATE items
   SET video = CONCAT('{"label":"', 
                      TRIM(SUBSTRING_INDEX(video, '|-|', 1)), 
                      '","link:"', 
                      TRIM(SUBSTRING_INDEX(video, '|-|', -1)), '"}')
 WHERE ...

Mudassir Hasan · Accepted Answer · 2014-01-05 03:23:41Z

0

This can be done in single step but for clarity i replacing in multiple steps.

Update items
Set video = replace(video,'XYREE','{"label":"XYREE')


Update items
Set video = replace(video,'|-|','","link":"')



Update items
Set video = concat(video,'"}')

answered Jan 5, 2014 at 3:23

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

2 Comments

zyrup Over a year ago

Yes thanks you were already right above. How to do this with the ending "} symbols in mysql?

Mudassir Hasan Over a year ago

What you want to do with ending symbol , using concat() I have added it to end of string

Collectives™ on Stack Overflow

MySQL: Select specific strings and replace on several locations

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related