MySQL function to replace comma separated values in column

Question

I have one column which has comma separated values(ids). I need to change corresponding values to them. I need MySQL function for replacing.

Here my table1.

id             name
-------------------
1              Lara

2              Smith

3              Antony

4              Ken

Here my table2

id            friends
---------------------

1             2,3

2             1,2

3             1,3

4             1,4,3,2

My MySQL function (get_names) should return names with comma separated instead of name'ids when I querying my table2.

ex.

when I call like this

select get_names('1,2,3,4','Lara,Smith,Antony,Ken','3,1');

it should return Antony,Lara

Here

My

1st parameter is - total user ids with comma separated order

2nd parameter is - total user name with comma separated order (same order like user ids)

3rd parameter is - any no. of user ids with comma separated any order

Please help me.

2 questions - 1. How many id-name pairs do you have in your actual data? 2. Can you do this replacement operation in application code instead? — shree.pat18
– shree.pat18, Commented Jun 12, 2014 at 8:31
@shree.pat18 Ans. 1. 100-500 data only 2. I couldnot able to do it in application. — Sathish Babu
– Sathish Babu, Commented Jun 12, 2014 at 8:32

user3722371 · Accepted Answer · 2014-06-12 08:31:31Z

3

I recommend changing table 2, so that you wouldn't have comma separated values:

id            friends
---------------------

1             2
1             3

2             1
2             2

3             1
3             3

4             1
4             4
4             3
4             2

In this case, you would have to change your MySQL function too, but it will be easier to retrieve values with a simple join.

answered Jun 12, 2014 at 8:31

user3722371

1456 bronze badges

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

4 Comments

Sathish Babu Over a year ago

hi. I will increase the row count in table and also when I showing this table in single row it will be very difficult

Strawberry Over a year ago

@SathishBabu Absolute nonsense

Sathish Babu Over a year ago

@Strawberry are u suggesting me to use above concept ?

Strawberry Over a year ago

Yes. See normalization - although the id column in the example above is a little unusual. It's more common to simply store friend pairs.

Akhil P M · Accepted Answer · 2014-10-10 08:48:25Z

0

comma separated values in mysql is the Violation of 1st NF

answered Oct 10, 2014 at 8:48

Akhil P M

1821 gold badge2 silver badges13 bronze badges

Collectives™ on Stack Overflow

MySQL function to replace comma separated values in column

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related