Solved

Parse Text Field From SQL Server Table - Denormalized Table

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
Nested Case statement 4 39
MS SQL: Getting all rows not just one , combining multiple queries 11 27
SQL, add where clause 5 24
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

831 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