Search code examples
mysqlsqlsql-updatesubstringconcatenation

How can I use multiple substring functions in an update statement?

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:

enter image description here

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!

Solution

  • 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.