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

Psuedo Query:

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

Expected result:

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

duplicate of stackoverflow.com/questions/2761583/…

qdinar
– qdinar

2017-04-09 15:23:27 +00:00
Commented Apr 9, 2017 at 15:23 — qdinar
– qdinar, Commented Apr 9, 2017 at 15:23

Daniel Vassallo · Accepted Answer · 2010-09-22 01:09:50Z

322

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, 2010 at 1:09

Daniel Vassallo

346k72 gold badges513 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Avatar Over a year ago

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.

BarneySchmale Over a year ago

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'

kiedysktos Over a year ago

@Daniel How about updating the answer according to comments above?

qdinar Over a year ago

special/separate question for possible null case : stackoverflow.com/questions/14020867/…

gus · Accepted Answer · 2020-04-03 13:58:33Z

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 3, 2020 at 13:58

gus

1571 silver badge4 bronze badges

1 Comment

Daniel Over a year ago

Whats funny is even though this doesn't answer the OP question very well, this is exactly what I needed.

Collectives™ on Stack Overflow

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

Before:

Psuedo Query:

Expected result:

2 Answers 2

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Before:

Psuedo Query:

Expected result:

2 Answers 2

4 Comments

1 Comment

Linked

Related