0

I'm trying to update my mySql database. the value of Value is 

{
    "Subject": "Subject here",
    "Content": "The content here",
    "TextContent": "Here text content goes",
    "To": "this is to user<to@example.com>",
    "From": "Name of User<from@example.com>"
}

Now what to do I've to search for From tag and then Replace the Name of User<from@example.com> to other user<other@example.com>.

The value after From tag is unknown for me. so i want to replace the whole things in between "" quotes.

for that what I'm doing currently

UPDATE `Table_Name`
SET Value= REPLACE(Value,'Name of User<from@example.com>','other user<other@example.com>')
WHERE Name = 'Name_of_field' && INSTR(Value,'From')>0;

I know this is wrong way to do this. please suggest me a way because here I'm using Name of User<from@example.com> that is unknown for me.

Kaushik
  • 2,072
  • 1
  • 23
  • 31
  • I would explore search-and-replace using a regular expression. Have a look at http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql and then if you need help with the regex I would post a new question just about that (tagged with 'regex') – Martin Wilson Jan 22 '14 at 06:05
  • @MartinWilson I'm going through that. I'm new to `mySql` so let me some time to understand that. – Kaushik Jan 22 '14 at 06:09
  • Is the order of fields (to, from, etc) always stays the same? – peterm Jan 22 '14 at 06:10
  • @peterm it may be or may not be. but if you can suggest something please do. in any scenario. – Kaushik Jan 22 '14 at 06:12

1 Answers1

2

This should do it

UPDATE table1
   SET value = REPLACE(value, 
                       SUBSTRING_INDEX(SUBSTRING_INDEX(value, '"From": "', -1), '"', 1),
                       'other user<other@example.com>')
 WHERE ...

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
  • Thank you for the answer. Can you please explain it ? – Kaushik Jan 22 '14 at 06:29
  • This is working fine with demo, but it is not working with my data. it is inserting the data in begin of the `Value` – Kaushik Jan 22 '14 at 06:36
  • You're welcome. In short chained `SUBSTRING_INDEX` (first gets everything to right from `"From: "`, and then the second gets everything to the left from the next double quote) extracts the value of `from` field, which is then used in replace. – peterm Jan 22 '14 at 06:37
  • Show the data on which it's not working correctly, preferably in a form of sqlfiddle. You can change my sqlfiddle and post a new link here in comments. – peterm Jan 22 '14 at 06:39
  • Good answer- simpler than using a regex (and UDF) – Martin Wilson Jan 22 '14 at 06:46
  • @kk12391 You didn't mention that you have backslashes in front of every double quotes which is **essential**. BTW why are you doing this??? Anyway see working sqlfiddle with your actual data http://sqlfiddle.com/#!2/4395b/1. – peterm Jan 22 '14 at 06:51
  • Thank you man I've done that. replaced `"From": "` with `\"From\":\"` and `"` with `\"`. and Done. Thanks for the help you've done. Thank you so much. :) – Kaushik Jan 22 '14 at 06:53
  • You're very welcome. I'm glad it worked out for you :) – peterm Jan 22 '14 at 06:54