CODEWITHSUNDEEP
mysql
Len_D
Len_D

Reputation: 1430

MySQL Replace portion of a string that appears after another string

I have a varchar field that contains notes. Each note item is on a separate line. An example appears below. I am trying to write a query that replaces the datetime var that appears after the string "Next Contact:" with a new date.

As you can see, there are other dates in the notes, so a simply ReqExp to search for date alone won't work for me. I am trying to figure out how to use RegExp to say replace whatever appears after 'Next Contact' to the end of line.

Here is what I have tried:

update funnel_deals set `note` = replace(substr(note,LOCATE('Next Contact:', `note`),35), 'Next Contact: 2014-12-12 11:15:00') where username = 'jfoster'

    update funnel_deals set `note` = replace(RIGHT(`note`, LOCATE('Next Contact:', `note`),35),'Next Contact: 2014-13-12 12:15:00') where username = 'jfoster'

Both return a syntax error:

The first query: [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') where username = 'jfoster'' at line 1

The second: [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '35),'Next Contact: 2014-13-12 12:15:00') where username = 'jfoster'' at line 1

Here is what the notes column contains:

Notes: aaa

Deal Name: Second V27 Quote
Contact Type: Demo-Set-Pres
Contact Disposition: 
Funnel Status: Presentation (Demo or Pitch)
Last Contact: 2014-10-08 10:25:30
Next Contact: 2014-10-12 10:15:00
Quote Amount: 1200
Deal Chances: 0

Regardless of how I change the query, the syntax errors return. Is this even the correct way to approach this?

Upvotes: 0

Views: 44

Answers (1)

peterm
peterm

Reputation: 92815

If the date/time format is fixed you can try something like this

UPDATE funnel_deals
   SET note = CONCAT(SUBSTRING_INDEX(note, 'Next Contact: ', 1),
       'Next Contact: 2014-12-12 11:15:00',
       SUBSTRING(SUBSTRING_INDEX(note, 'Next Contact: ', -1), 20))
 WHERE username = 'foster';

Here is SQLFiddle demo

Upvotes: 1

Related Questions