CODEWITHSUNDEEP
mysqlselect
user2477311
user2477311

Reputation: 155

mySQL: Is it possible to make a SELECT statement, when the value in the DB is seperated with commas?

MY DB entry for the field data in the table productinfo is this for example: 

66523,665893,745896321

Now I want a SELECT statement which gives me the hole entry:

For example: 

SELECT * from productinfo WHERE products="66523"

Upvotes: 1

Views: 65

Answers (4)

vjy
vjy

Reputation: 1204

Have you tried like this?

SELECT * from productinfo WHERE products like "66523,%" OR products like  "%,66523,%" OR products like  "%,66523" or products="66523";

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

select * from productinfo where FIND_IN_SET ('66523', products);

fiddle

Upvotes: 3

Vivek Sadh
Vivek Sadh

Reputation: 4268

Use this:-

SELECT * from productinfo WHERE products like "66523,%" OR  products like ",66523,%" OR  products like "%,66523"

It will match anything containing 66523, + something. OR , + 66523, + something. OR something + 66523(last number). It will make sure that whole number is matched.

SQL FIDDLE DEMO

Upvotes: 0

xlecoustillier
xlecoustillier

Reputation: 16351

Try:

SELECT *
FROM   productinfo
WHERE  Concat(',', products, ',') LIKE '%,66523,%'

In this fiddle, you can check that the first three rows are returned because they contain the 66523 product, but not the 4th one, which contain a product number containing 66523.

But it's a really bad habit to store more than one piece of data into one single field. You should preferably split this data into a new table, using foreign keys. Otherwise, you will be stuck with similar workarounds, without any efficient way to use indexes, and therefore low performances.

Upvotes: 2

Related Questions