How can I append a string to an existing field in MySQL?

Question

I want to update the code on all my record to what they currently are plus _standard any ideas?

So for example if the codes are apple_1 and apple_2 I need them to be apple_1_standard and apple_2_standard

Before:

id   code
------------
1    apple_1 
1    apple_2

update categories set code = code + "_standard" where id = 1;

id   code
----------------------
1    apple_1_standard 
1    apple_2_standard

duplicate of http://stackoverflow.com/questions/2761583/appending-data-to-a-mysql-database-field-that-already-has-data-in-it — qdinar, Apr 09 '17 at 15:23

score 304 · Accepted Answer · answered Sep 22 '10 at 01:09

304

You need to use the CONCAT() function in MySQL for string concatenation:

UPDATE categories SET code = CONCAT(code, '_standard') WHERE id = 1;

answered Sep 22 '10 at 01:09

Daniel Vassallo

84

Just in case somebody runs into the same issue as I did: If the field `code` is NULL by default you need to use: `UPDATE categories SET code = CONCAT(IFNULL(code,''), '_standard') WHERE id = 1;` Otherwise the concat will always result in NULL. – Avatar May 04 '15 at 17:28
4

Alternatively you can use CONCAT_WS which skips NULL values. For example `SELECT CONCAT_WS(', ','First name',NULL,'Last Name');` gives 'First name, Last Name' – BarneySchmale Aug 08 '16 at 11:00
1

@Daniel How about updating the answer according to comments above? – kiedysktos Mar 28 '17 at 08:08
1

special/separate question for possible null case : http://stackoverflow.com/questions/14020867/mysql-update-append-data-into-column – qdinar Apr 09 '17 at 15:29

score 15 · Answer 2 · answered Apr 03 '20 at 13:58

15

Update image field to add full URL, ignoring null fields:

UPDATE test SET image = CONCAT('https://my-site.com/images/',image) WHERE image IS NOT NULL;

answered Apr 03 '20 at 13:58

gus

7

Whats funny is even though this doesn't answer the OP question very well, this is exactly what I needed. – Daniel Sep 17 '20 at 17:58