Jump to content

Query for something in Text


SharkBait

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?

Link to comment
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.
Link to comment
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 :)
Link to comment
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]
Link to comment
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 :)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.