1

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"
xlecoustillier
  • 16,183
  • 14
  • 60
  • 85
user2477311
  • 155
  • 2
  • 2
  • 11
  • 2
    This seems to be a surprisingly common practice. If you don't store raw data in your database, you can't benefit from database features. You're basically using your DB as an overcomplicated file system ;-) – Álvaro González Jun 21 '13 at 10:02
  • You should normalize your database, so you can do a simple join, instead of building work arounds. – dognose Jun 21 '13 at 10:52
  • [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Jun 21 '13 at 12:58

4 Answers4

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

fiddle

Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70
  • @Praveen +1 Ohh.. I remember using this function for my recent project. It didn't came to my mind. Perfecto! :) – Vivek Sadh Jun 22 '13 at 08:23
2

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.

xlecoustillier
  • 16,183
  • 14
  • 60
  • 85
0

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

Vivek Sadh
  • 4,230
  • 3
  • 32
  • 49
0

Have you tried like this?

SELECT * from productinfo WHERE products like "66523,%" OR products like  "%,66523,%" OR products like  "%,66523" or products="66523";
vjy
  • 1,184
  • 1
  • 10
  • 24
  • What if the list has no commas, only one number 66523 in it? – Bill Karwin Jun 21 '13 at 12:58
  • @BillKarwin in that case need one more `OR` condition, see my updated answer. This will work, but I am not sure, this a best solution, as this query is too long. – vjy Jun 21 '13 at 13:09