Solved

Parse Text Field From SQL Server Table - Denormalized Table

Posted on 2008-06-16
4
659 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 shrink a transaction log file down to a reasonable size.

733 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