CODEWITHSUNDEEP
phpmysql
devsoft
devsoft

Reputation: 269

MySQL check if value exist in row, separated by minus

I have a table with ProductIDs and associated Accessories Ids separated by minus.

 ProductId    Accessories
 1            1-2-3-
 2            3-4-
 3            1-2-3-
 4            2-3-

How can I get ProductIds where Accessories Id = 2

I tried this query but it is not working correct

SELECT *
FROM `products`
WHERE '-' + Accessories + '-' LIKE '%-2-%'
LIMIT 0 , 30

Database can't be changed.

Upvotes: 1

Views: 565

Answers (4)

Dan Soap
Dan Soap

Reputation: 10248

Although the following solution also has problems with using an index, I wanted to add it for completeness:

SELECT *
  FROM `products` 
 WHERE find_in_set('2', replace(`accessories`, '-', ','))
 LIMIT 0, 30;

Explanation:

replace(accessories, '-', ',') turns the accessories lists into a comma separated list, for example 1-2-3- will be converted to 1,2,3,

find_in_set(value, set) can then be used to see if a value is contained in set.

As I said earlier, no index can be used, but the performance should be measured on the live data set.

Upvotes: 2

Marc B
Marc B

Reputation: 360762

Assuming that those values ALWAYS have a trailing dash, then you've got a few cases to cover:

1) single value: ... WHERE Accessories = CONCAT($value, '-')
2) value at start: ... WHERE Accessories LIKE '$value-%'
3) value in the middle: ... WHERE Accessories LIKE '%-$value-%'
4) value at the end: ... WHERE Accessories LIKE '-$value-$'

which'd end up being this in the full(er) SQL:

SELECT ...
FROM ...
WHERE 
    (Accessories = CONCAT($value, '-')) OR             // single
    (Accessories LIKE CONCAT($value, '-%')) OR         // start
    (Accessories LIKE CONCAT('%-', $value, '-%')) OR   // middle
    (Accessories LIKE CONCAT('%-', $value, '-'))       // end

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212452

If you absolutely have to do this incredibly bad practise that will complicate your coding life until the day you retire:

SELECT * 
  FROM products 
 WHERE CONCAT('-',Accessories) LIKE '%-2-%' 
  LIMIT 0 , 30

Upvotes: 8

Ted Hopp
Ted Hopp

Reputation: 234847

I think you almost had it. Try this instead:

SELECT *
FROM products
WHERE Accessories LIKE '%-2-%'
LIMIT 0 , 30

Upvotes: 0

Related Questions