Query for something in Text

[SharkBait]

Hi,

I have a field in my database that is a text type and inside the fields the values are stored like:

Model | PNS
--------------------------------------
Item1 | C1,C2,C4,C10,C34,C35,C36
--------------------------------------
Item2 | C1,C4,C34,C55,C56,C57
--------------------------------------
Item3 | C1,C4,C56,C59,C98
--------------------------------------

I have SELECT * FROM parts WHERE PNS = ????? How do I just make a query to look for the C34?

[jworisek]

thats definitely not the most efficient way of storing this information, but you can use

[code]
SELECT * FROM parts WHERE PNS LIKE '%C34%'
[/code]

consider looking into the "SET" type to store the information if possible. It looks to me like you just want to store a list of information and sorting through text in the method I show above is not very efficient.

[SharkBait]

[!--quoteo(post=362351:date=Apr 6 2006, 01:22 PM:name=jworisek)--][div class=\'quotetop\']QUOTE(jworisek @ Apr 6 2006, 01:22 PM) [snapback]362351[/snapback][/div][div class=\'quotemain\'][!--quotec--]
thats definitely not the most efficient way of storing this information, but you can use

[code]
SELECT * FROM parts WHERE PNS LIKE '%C34%'
[/code]
[/quote]

Yea I do realize that is note totally efficient. But I have like 55 items that point to the same Description but have a different REF_PN

So all those in the Item1 are the same part, but in a different location.

I could change it so that C14, C15, C28, C29 etc are all seperate items in the database, but it seems silly because the only difference between those 4 parts is the Ref_pn.

If I use LIKE that will also pull out things like C345, C345, CC34 too wont it?

Though any suggestions on how I should store thses values would be grealty appreciated

Something like this pershaps?
id | Ref_pn | Description | TR-PN
------------------------------------------------
1 | C3 | Cap 10uF 10V 10% | 25-000004-00
------------------------------------------------
2 | C5 | Cap 10uF 10V 10% | 25-000004-00
------------------------------------------------
3 | C6 | Cap 0.1uF 12V 5% | 25-000005-01
------------------------------------------------

If that helps :)

[fenway]

There's no question that these should be represented as different DB records; however, for the time being, you can use the following query (UNTESTED) to returning proper matches (will match C34, not C345):

[code]SELECT PNS, LOCATE( ',C34,', CONCAT(',',PNS,',') ) AS matchPos FROM parts HAVING matchPos > 0[/code]

[SharkBait]

[!--quoteo(post=362372:date=Apr 6 2006, 02:33 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Apr 6 2006, 02:33 PM) [snapback]362372[/snapback][/div][div class=\'quotemain\'][!--quotec--]
There's no question that these should be represented as different DB records; however, for the time being, you can use the following query (UNTESTED) to returning proper matches (will match C34, not C345):

[code]SELECT PNS, LOCATE( ',C34,', CONCAT(',',PNS,',') ) AS matchPos FROM parts HAVING matchPos > 0[/code]
[/quote]

I did the proper way and put them into their own database and their own items and link 2 other tables to it.

Thanks :)