MySQL Replace portion of a string that appears after another string

Question

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?

Why not do this in the application layer? Better yet, why not store information related to these notes into separate database fields? This seems like a very unwieldy way to manage this data. — Mike Brant
– Mike Brant, Commented Oct 8, 2014 at 17:25
This is someone else's application. We provide scheduling changes and need to update their system. I'm stuck with what it is. — Len_D
– Len_D, Commented Oct 8, 2014 at 17:27

peterm · Accepted Answer · 2014-10-08 19:05:59Z

1

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

answered Oct 8, 2014 at 19:05

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Replace portion of a string that appears after another string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related