CODEWITHSUNDEEP
mysqlsqlselectreplace
zyrup
zyrup

Reputation: 717

MySQL: Select specific strings and replace on several locations

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.

Upvotes: 0

Views: 90

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

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,'"}')

Upvotes: 0

peterm
peterm

Reputation: 92815

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 ...

Upvotes: 1

Related Questions