I have a table with a column called candidate_name
in which the names are formatted as lastname, firstname
. I'd like to update the column so that the names are in firstname lastname
format.
I wrote this query to see if the string functions were working correctly:
SELECT
SUBSTRING(candidate_name, 1, LOCATE(',', candidate_name) - 1) AS last,
SUBSTRING(candidate_name, LOCATE(',', candidate_name) 2, LENGTH(candidate_name) - LOCATE(',', candidate_name) - 1) AS first
FROM
colorado_project.candidates
WHERE
candidate_name = 'AHREND, JARED';
This returns the strings into new columns as expected:
But I don't know how to use these in an update statement. I've tried many statements similar to this:
UPDATE candidates
SET candidate_name = SUBSTRING(candidate_name, 1, LOCATE(',', candidate_name) - 1),
SUBSTRING(candidate_name, LOCATE(',', candidate_name) 2,
LENGTH(candidate_name) - LOCATE(',', candidate_name) - 1)
WHERE candidate_name = 'AHREND, JARRED';
Pretty sure I've gotten every error known to mankind at this point, but the main error I get is
'1292 Truncated incorrect DOUBLE value 'AHREND'.
I've searched around and it seems like this error is often associated with comparing a string value to a number value, but I don't understand by the substring functions would be returning a number value now, when they were returning strings before? Or maybe that's not whats going on here. Any help would be appreciated. Thanks!
CodePudding user response:
You must concatenate the 2 parts of the name:
UPDATE candidates
SET candidate_name = CONCAT(
SUBSTRING(candidate_name, LOCATE(',', candidate_name) 2, LENGTH(candidate_name) - LOCATE(',', candidate_name) - 1),
' ',
SUBSTRING(candidate_name, 1, LOCATE(',', candidate_name) - 1)
)
WHERE candidate_name = 'AHREND, JARED';
See the demo.
or, simpler with SUBSTRING_INDEX()
:
UPDATE candidates
SET candidate_name = CONCAT(
TRIM(SUBSTRING_INDEX(candidate_name, ',', -1)),
' ',
SUBSTRING_INDEX(candidate_name, ',', 1)
)
WHERE candidate_name = 'AHREND, JARED';
See the demo.