Searching a comma separated string (Normalization question)

[lespritdelescalier]

I am trying to read data from a table created by another application developer. In the table, there is a field for Part IDs. This field can have one or many Part IDs in it. If there are more than one, each Part ID is separated by a comma.

My issue is that we have similar Part IDs, and that using LIKE in a SQL statement will not necessarily return the result that I need. An example would be as follows:

My search criteria: 1234

Field value: 1111,1222A,1233,1234A,1235

If I use LIKE, the SQL statement will return a result, but I don't want it to because it doesn't exactly match one of the Part IDs. I have attempted to add commas to the ends of the search criteria, but the SQL statement fails to return results. It also won't handle situations where the Part ID is first or last in the field.

I would like to recommend to the application dev that the table be normalized, or that two tables be used (one with the rest of the tables details, and one that links the Part IDs to each record). This will require some redevelopment and delay the release of the other application, but I would like things to be set up correctly.

Thanks for any feedback that you might have!

 

[isladogs]

Try including the commas in your search string i.e. ",1234,"
You may need to modify if there could be spaces before/after the comma

You definitely should get the multi-valued field changed in any future redesign

 

[Mark_]

Please take a look HERE for a function that may do exactly what you want on a "Per record" basis.

Down side is you would have to look through ALL records to see if ANY have the matching result.

P.S. Normally you would reply to the person who responded in a new post, not update your original post. This makes the thread easier to understand. If you DO have a primary post you are planning to update (say an example of how to do something) you would normally have a block clearly defined that other readers can easily identify as "Being updated".

 

[lespritdelescalier]

Please take a look HERE for a function that may do exactly what you want on a "Per record" basis.

Down side is you would have to look through ALL records to see if ANY have the matching result.

P.S. Normally you would reply to the person who responded in a new post, not update your original post. This makes the thread easier to understand. If you DO have a primary post you are planning to update (say an example of how to do something) you would normally have a block clearly defined that other readers can easily identify as "Being updated".

I only changed two words in my original post. I didn't reply to anyone.

I think I figured out my issue, but if it doesn't work, I will have a look at your suggestion.

Thanks!

 

[isladogs]

No idea what you changed in your first post
Did you mention commas before you edited it?
If so, I missed it.

 

[Mark_]

OPs post has Last edited at the bottom without a reason specified.

Often I've seen people do that when they update their original post based on subsequent responses. Tends to make the subsequent responses less meaningful.