Mysql find_in_set of array separator

Question

I have column user_orders we are storing like

[1][2][3][5]   // Expected output 1,2,3,5
[25][27]       // Expected output 25,27
[2]            // Expected output 2

I don't understand why my master store like above format instead of comma separator data, Is this for security reason?

How replace this with 1,2,3,5 to use in find_in_set(u.id, c.user_orders)?

I tried like this below url

https://stackoverflow.com/questions/27262855/mysql-find-in-set-slash-separator

Tim Biegeleisen · Accepted Answer · 2018-11-08 15:42:07Z

2

Rather than trying to convert [1][2][3][4] to a CSV format, an easier way would be to use REGEXP. For example, if you wanted to search for an id of 3, you could use:

SELECT *
FROM yourTable
WHERE user_orders REGEXP '[[:<:]]3[[:>:]]';

If you really wanted to convert your data to pure CSV, you could do a series of replacements, e.g.

SELECT *
FROM yourTable
WHERE FIND_IN_SET('3', REPLACE(REPLACE(REPLACE(user_orders, '][', ','),
    '[', ''), ']', '')) > 0;

But in general, you should avoid storing either [1][2][3][4] or 1,2,3,4, because both are unnormalized data, and thereby greatly limit MySQL's ability to do things.

answered Nov 8, 2018 at 15:42

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

VijayaKrishna Over a year ago

But my mater storing data as above format only, I don't know why?

Tim Biegeleisen Over a year ago

@VijayaKrishna Then hopefully my answer lets you workaround this for the time being. I am only suggesting what is best practice; sometimes we have to live with the bad design decisions made by other people :-)

Collectives™ on Stack Overflow

Mysql find_in_set of array separator

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related