SQL Server : need assitance parsing delimted data and returning a long concatenated string

Question

Ok, I have a table MFBRW with a column SERV_CDE_LIST_OSI

This list is separated by a space; each code is 2 characters in length. The entire string can be 0 – 100 codes long.

ID | Active_Products__C | SERV_CDE_LIST_OSI
____________________________________________
1  |                    |   AA AE AG
2  |                    |   AA AG
3  |                    |   AA AE LP

And I have another table AuthCodes with columns AuthCode and ServiceName

ID | AuthCodes | ServiceName
__________________________________
1  |    AA     |     Beer
2  |    AE      |    Coffee
3  |    AG      |    Coke
4  |    LP      |    Water

I need to get the AuthCode.ServiceName for each code in MFBRW.SERV_CDE_LIST_OSI and write that name, as one long string @ MFBRW.Active_Products__C

ID | Active_Products__C   | SERV_CDE_LIST_OSI
____________________________________________

1  |  Beer; Coffee; Coke  |     AA AE AG
2  |  Beer; Coke;         |     AA AG
3  |  Beer; Coffee; Water;|    AA AE LP

I thought this might work, but is does not return anything.

UPDATE Salesforce.[dbo].[MFBRW]
SET MFBRW.Active_Products__C = AuthCodes.ServiceName
FROM [MFBRW] 
LEFT JOIN [AuthCodes] 
ON [MFBRW].SERV_CDE_LIST_OSI = [AuthCodes].AuthCode
WHERE  (((CharIndex(([MFBRW].[SERV_CDE_LIST_OSI]),([AuthCodes].[AuthCode])))>0));

Please assist.

That's why you never should store multiple values in one column. Your table structure doesn't even satisfy 1st normal form. — Rahul, Oct 29 '14 at 22:09
Required reading: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Michael McGriff, Oct 29 '14 at 22:17

Mike · Accepted Answer · 2014-10-31T02:26:28.603

0

Complaints against normalizing data aside (because it's often out of our control), here's a generalized solution:

Create Table List (ID int Primary Key, Codes nvarchar(300));
Create Table Code (Code nchar(2), Name nvarchar(100));

Insert List
Values
 (1, 'AA AB AC AD')
,(2, 'AA AC')
,(3, 'AB');

Insert Code
Values
 ('AA', 'Apple')
,('AB', 'Banana')
,('AC', 'Cantaloupe')
,('AD', 'Dough');
Go

With Matches As
(
Select List.ID, Code.Code, Code.Name
From List
  Join Code
  On List.Codes Like '%' + Code.Code + '%'
)
Select List.ID
  ,Names = (
      Select (
        Select Name + '; '
        From Matches
        Where Matches.ID = List.ID
        For XML Path(''), Type
      ).value('.[1]', 'nvarchar(max)')
  )
From List

Here's the SQL Fiddle.

Edit: this SQL Fiddle is a slight modification of the above example - it executes an Update against the hypothetical List table.

edited Oct 31 '14 at 02:26

answered Oct 29 '14 at 22:33

Mike

3,641
3
29
39

I understand that the data is not the way the 1st norm requires, it is a string that is spit out of an old mainframe.... I haven't much control over it. Thanks Mike for this code, it is great. I am wondering, if I want to take it one step further, and write that data back to the list table, how would I do that? Thanks. – Jerry Clifft Oct 30 '14 at 21:57
I feel your pain. The format of the original data is almost out of our control. I added an example of how to `Update` with the original scenario. – Mike Oct 31 '14 at 02:32

SQL Server : need assitance parsing delimted data and returning a long concatenated string

1 Answers1