search in mysql using explode

[meloncolie]

hey gurus,

I have a problem... I want to query a statement in my database where the datafield containing id numbers... for example my datafield contain "5, 12, 1"

if i used LIKE "%1%" i just want to search only the datafields that contain 1 but the query also search 12...

how can I explode "," so I can search the specific idnumber...

Thanks..

P.S.
My English Sucks :D

[chazzy]

sounds more like you have a poorly designed table structure. you should never have a field contain a list of values, it's not searchable.

you should look into restructuring your table to have a separate lookup table with this info.

[meloncolie]

this is not the help im expecting

[chazzy]

So you want to hear the brutal truth?

No. You cannot use anything like explode to search through the contents. Your best bet would be to use a regular expression to match exacts.

But this is not the way you should be searching.

[pekarna]

There's one at http://ondra.zizka.cz/stranky/programovani/sql/mysql_stored_procedures.texy , but not sure if it can serve your purposes.

[themarty]

this is not the help im expecting

Still, chazzy is helping you way better than pekarna.

Storing multiple IDs in one column goes against the first normalization form. The biggest favour you can do yourself is take chazzy's advise and restructure your tables now: it will save you heaps of time later.

[chazzy]

guys, topics 2.5 years old (way before stored procs were in a mysql developers vocab)

[themarty]

ah, i never looked at the date of the other posts.... i just replied to a post that had today's date.. :)

[gnh1201]

I have the same problem. How do I solve this question?

[ssystems]

I have the same problem. How do I solve this question?

Consider starting a new thread. This was started 2006. :) Anyway read previous posts. Take Chazzy's advice and not the one by Pekarna