Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

Parse Text Field From SQL Server Table - Denormalized Table

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)
Text_Value    TEXT

and the following values
Record_ID   Text_Value
   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:
Support_ID        VARCHAR(10)
Support_Value  VARCHAR(50)

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.



0
mrihm
Asked:
mrihm
  • 2
  • 2
1 Solution
 
appariCommented:
try

Select T1.Record_ID, T1.Text_Value, T2.Support_ID, T2.Support_Value from
Incidents T1 join support T2
on '|' + T1.Text_Value  + '|' like '%|' + convert(varchar(5)|T2.Support_ID) + '|%'
0
 
appariCommented:
sorry try this

Select T1.Record_ID, T1.Text_Value, T2.Support_ID, T2.Support_Value from
Incidents T1 join support T2
on '|' + T1.Text_Value  + '|' like '%|' + convert(varchar(5), T2.Support_ID) + '|%'
0
 
mrihmAuthor Commented:
Thanks appari,

I'll try that tomorrow

0
 
mrihmAuthor Commented:
thanks appari,

this worked but unfortunately it looks like the data stored in the text field is a lot more complicated in that it comes from a couple of different support tables and the order (e.g. every third field) determines which support table is referenced. At this point I am thinking that I will handle the parsing in the calling application and not on the database itself.

Again, thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now