CODEWITHSUNDEEP
mysqlstring
Deniss Kozlovs
Deniss Kozlovs

Reputation: 4841

MySQL prefix SET field values with custom string

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

Upvotes: 1

Views: 532

Answers (1)

sgeddes
sgeddes

Reputation: 62831

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.

Upvotes: 2

Related Questions