String pattern find and set as variable

[m0nkie]

Hello,

I was wondering if anyone could suggest how i may accomplish this.
I wanted to have users enter in a pattern in a note field of an application and then run an AFTER INSERT trigger that would "read" the note and look for a pattern and then set the value within that pattern as a variable.

AN example is this.
if an user inputs ##jedwards## in the note field, then the note would be inserted and then the trigger would assign the note to that user, jedwards. Currently the application is a closed app and i can only manipulate the backend. this is one way that notes can be assigned to users other than the ones entering the note, so they can be alerted of the note, such as a callback.

i was contemplating using some sort of preg_replace function i can install, lib_mysqludf_preg, but i am not sure this will apply too well.
since the user name within the '##' grouping is a variable field, it makes this a bit tricky. i can however force users to enter in the username in any pattern suggested, but i am not sure how to parse the record to pull it out and then update the note to be assigned to the username in that variable...

any ideas?

thanks

 

[jpadie]

You can probably do the whole thing with a mysql stored procedure rather than use a trigger.

Do the initial insert inside the procedure
Parse the notes field
Test to see whether a corresponding user exists and
If so write to that user's row.