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

Question

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!

[Why should I "tag my RDBMS"?](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) - please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Aug 28 '22 at 05:49
I'm not sure how to find the table definition, I'm still new to this. But the 'candidates' table is made up of text columns, with the exception of the 'candidate_name' column that I'm working with here. I changed it to a VARCHAR column in an effort to get this to work, but it didn't help. I've also tagged MySQL and will remember to do that in the future. — arctic_radar, Aug 28 '22 at 20:22

score 1 · Accepted Answer · answered Aug 28 '22 at 19:56

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.

This worked, thank you! I thought about using CONCAT, but convinced myself that wouldn't work for some reason. Now, looking at it, it makes total sense! — arctic_radar, Aug 28 '22 at 20:57

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

1 Answers1