I have a database that contains a text field which holds a pipe delimited list of foreign key tables (the design ain't pretty but unfortunately I cannot change it) which I need to parse so that I can join them to the corresponding record in the support table.
So to illustrate, let's assume I have a table, Incidents, with the following columns:
Record_ID INT (identity)
and the following values
1 Value 1|Value 2|Value 5||Value 11
2 Value 2|Value 3|Value 6|
The delimited items in the Text_Value field come from a support table, Incindent_Type, with the following columns:
What's the easiest way to deal with data such as this if I want
a) create a join to join each value in the Text_Value field to the corresponding field in the Incindent_Type support table?
b) select records from the Incident table based on one or more values from the Incindent_Type table? Note that I cannot use a straight in-string search function because Value 1 would also be true for value 11, value 12, etc.
I appreciate any input you could give me.