Solved

Parse Text Field From SQL Server Table - Denormalized Table

Posted on 2008-06-16
4
627 Views
Last Modified: 2010-04-21
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
Comment
Question by:mrihm
  • 2
  • 2
4 Comments
 
LVL 39

Expert Comment

by:appari
ID: 21799254
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
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 21799258
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
 
LVL 5

Author Comment

by:mrihm
ID: 21799399
Thanks appari,

I'll try that tomorrow

0
 
LVL 5

Author Closing Comment

by:mrihm
ID: 31467838
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now