-1

I'm looking for a way to replace just a portion of a string with a part of a string for same row via MySQL query.

For example, if I have records that title='blablabla - XX.YY.ZZZZ' and I want to add only the string, ZZZZ, to a field - extra_fields but with a format '[{"value":"ZZZZ"}]'

Original table:

title | extra_field
--------------------
19.02.1996 | [empty]
13.05.1815 | [empty]
22.03.2016 | [empty]

After the update I need:

title | extra_field
-------------------
19.02.1996 | Year was 1996 
13.05.1815 | Year was 1815 
22.03.2016 | Year was 2016
GGGorast
  • 31
  • 9
  • possible duplicate http://stackoverflow.com/questions/5956993/mysql-string-replace – Aroniaina Nov 08 '16 at 11:17
  • not quite. I need a way to extract only ZZZZ part so it can be replaced – GGGorast Nov 08 '16 at 11:19
  • UPDATE your_table SET your_field = REPLACE(your_field, 'ZZZZ', '[{"value":"ZZZZ"}]') WHERE your_field LIKE 'ZZZZ' – Aroniaina Nov 08 '16 at 11:21
  • @Aroniaina I updated original post with visuals – GGGorast Nov 08 '16 at 11:30
  • now, it is clear, remember to be clear if you ask a question – Aroniaina Nov 08 '16 at 11:33
  • UPDATE yourtable SET extra_field = CONCAT('Year was ', SUBSTRING(title,7)) – Aroniaina Nov 08 '16 at 11:41
  • Just as a warning: if that is actually all you want to do: do not do it! It will duplicate your data (you will have the information "1996" twice in your row, and the text "Year was" in every row) That will have the effect that, when you later change "title" (or "extra_field"), the values get out of sync; and if you want to use the text "The year was", you have to update every row. Do that in the query, e.g. `select title, CONCAT('Year was ', SUBSTRING(title,7)) as extra_field from...` (or in php). If you just update some specific rows (or if it was just an example), this might not be relevant. – Solarflare Nov 08 '16 at 12:32
  • @Aroniaina You saved my life! I used UPDATE yourtable SET extra_field = CONCAT('Year was ', SUBSTRING(title,-4)) so it wont matter all the garbage that is written before. – GGGorast Nov 08 '16 at 15:40

1 Answers1

0

The query solution

UPDATE yourtable SET extra_field = CONCAT('Year was ', SUBSTRING(title,7))

OR 

UPDATE yourtable SET extra_field = CONCAT('Year was ', SUBSTRING(title,-4))
Aroniaina
  • 1,252
  • 13
  • 31