MySQL prefix SET field values with custom string

Question

I have a MySQL 5 database table field media like set('audio','video','photo') What i need to do, is in single SELECT statement prefix it's values with some custom string and space after it, if any of the values are present. For example:

audio,video becomes mediaaudio mediavideo

photo becomes mediaphoto

The specifics of the data does not require an external relationship table to be made for corresponding values, so set is sufficient for the current task. I need to prefix them to uniquely identify them later in search results.

Real example:

id media

1 audio,video

2 audio

3 video

4 photo,video

5

Expected result:

id media

1 mediaaudio mediavideo

2 mediaaudio

3 mediavideo

4 mediaphoto mediavideo

5

are the values separated by a comma or not? i think if you have an enum field, you can only have one value per column. — John Woo
– John Woo, Commented Feb 11, 2013 at 15:41
Easy with concat / concat with a case statement I think... unless you are storing those comma separated values in a single field? — Matthew
– Matthew, Commented Feb 11, 2013 at 15:42
show us sample records (maybe 3 or 4 records) with your desired result. — John Woo
– John Woo, Commented Feb 11, 2013 at 15:42

sgeddes · Accepted Answer · 2013-02-11 15:56:40Z

2

Here's one way to do it with INSTR, CONCAT, REPLACE and LENGTH:

SELECT ID, 
  CASE WHEN INSTR(YourField, ',') > 0
    THEN CONCAT('media', REPLACE(YourField, ',', ' media'))
    WHEN LENGTH(YourField) > 0
    THEN CONCAT('media', YourField) 
    ELSE ''
   END media
FROM YourTable

And the Fiddle.

Good luck.

edited Feb 11, 2013 at 15:56

answered Feb 11, 2013 at 15:51

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

MySQL prefix SET field values with custom string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related