Reputation: 69
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
Upvotes: 0
Views: 291
Reputation: 522311
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.
Upvotes: 2