MySQL move value from within text column to new INT column

Question

So, I would like to improve a shoutbox plugin for MyBB and do the following in pMA.

Private shouts are stored as TEXT/VARCHAR: "/pvt userid message". What I want to achieve is to:

• move value in userid to the new INT column touid.
• remove the /pvt id prefix

Earlier the code used sscanf($message, "/pvt %d", $userID). The query would be something like UPDATE shouts SET touid=???, message=??? WHERE message LIKE '/pvt %'.

@edit: Examples:

/pvt 55 Hello world - this is a private shout!

/pvt 675 Another pm.

This is not a private shout

Answer 1

The first part is not so hard:

update shouts
    set touid = substring_index(message, '/pvt ', -1) + 0
    where message LIKE '/pvt %';

This splits the string on '/pvt ', takes the second part and converts it to an integer. MySQL does silent conversion, so it converts the leading number.

An alternative takes advantage of the fact that the pattern is at the beginning:

update shouts
    set touid = substr(message, 6, 1000) + 0
    where message LIKE '/pvt %';

You can get rid of this part of the string by replacing it with nothing:

update shouts
    set touid = substring_index(message, '/pvt ', -1) + 0
        message = replace(message,
                          concat('/pvt ', substring_index(message, '/pvt ', -1) + 0),
                          '')
    where message LIKE '/pvt %';