Jump to content

Archived

This topic is now archived and is closed to further replies.

SharkBait

Query for something in Text

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[!--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 :)

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
[!--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 :)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.