How to write a query to add a prefix to a comma separated string in MySQL?

• I have a table in a MySQL database with a column id.
• The id column has comma separated values stored in it in VARCHAR format.
• I want to add a prefix C to every value in the output of the query.

Ex:

Current Query: select id from table; 
Current Output: 1,2,3,4

Desired Output: C1,C2,C3,C4

Answer

This all sounds like a bad idea, but in some situations it might be needed.

It seems that treating id as a string, and applying string functions to it, is the best solution. If you want to do this non-permanent, you can do it this way:

SELECT
  id AS oldId,
  IF(LENGTH(id) > 0, CONCAT('C', REPLACE(id, ',', ',C')), '') AS newId
FROM
  table;

This works by first checking if there’s something in id. If there isn’t, it is left empty. If there is content, a C is added at the front and after every comma.

if you want to make this permanent, you could do:

UPDATE
  table
SET
  id = IF(LENGTH(id) > 0, CONCAT('C', REPLACE(id, ',', ',C')), '');

This changes the content of your table. Apply with care and only once.