geniestacks geniestacks - 1 year ago 105
MySQL Question

MySQL Replace String "John Doe" with "John D"

Have a large name table all as string "Name Surname"

Need to update the entire table, setting the string to be "Name FirstLetterOfSurname" ie. "John Citizen" becomes "John C".

Seems easier to do via PHP eg.

$name = "John Doe";
$expl = explode(' ', $name);
echo $expl [0].' '.$expl[1][0];
John D

wondering about a query to just update the database.

Help Appreciated?

Answer Source

I'd find the location of the space, and take a substring of one character more than that:

SELECT SUBSTR(name, 1, LOCATE(' ', name) + 1)
FROM   mytable

Of course, this can also be done as an update instead of just querying the string in the right format:

UPDATE mytable
SET    name = SUBSTR(name, 1, LOCATE(' ', name) + 1)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download