parsing comma seperated values in MS-SQL (no csv or such)

Question

I use a closed source commercial application that uses an MS-SQL database. I regularly have to query this database myself for various purposes. This means the table and database design is fixed, and I can't do anything about it at all. I just have to live with it. Now I have two tables with the following layouts (abstracted, not to discredit the software/database designer)

t1: ID (int), att1(varchar), att2(varchar), .... attx(varchar)

t2: ID (int), t1_ids(varchar)

Now the contents of this t1_ids is (shudder) a comma separated list of t1 id's. (for example 12, 456, 43, 675, 54). What I want to do is (you guessed it) join those two tables.

Fortunately for me, these are very small tables, and I don't care about performance in terms of complexity at all (could be O(n^m) as far as I care).

Ideally I would like to make a view that joins these two tables. I don't have any requirements for inserting or updating, just for select statements. What would be the easiest and clearest (in terms of maintainability) way to do this?

possible duplicate of [T-SQL: Opposite to string concatenation - how to split string into multiple records](http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor) — Lucero, Dec 20 '10 at 11:41

bernd_k · Accepted Answer · 2010-12-20T12:52:41.937

1

To get the first and last too use this:

select * 
from t1 
    join t2 on '%,' + t1.ID + ',%' like ',' + T2.t1_ids + ','

It doesn't matter if T2.t1_ids start or end with . The valid values are enclosed by commas.

edited Dec 20 '10 at 12:52

answered Dec 20 '10 at 11:55

bernd_k

11,558
7
45
64

Same comment as Jani, this doesn't work for the first and the last item, as they don't have commas on both sides – Martijn Dec 20 '10 at 12:23
@Martijn Gotcha. Fixed my code. I really worked with that pattern in the past. – bernd_k Dec 20 '10 at 12:54

score 0 · Answer 2 · edited May 23 '17 at 09:58

EDIT: I realised after posting this answer the PARSENAME function can only return one part of the parsed string, so it's not a useful as I thought it would be in your situation.

Searching SO for alternative solutions I came across an interesting answer to this question: Split String in SQL

If you can add triggers to your database then you could call the split string on INSERT, UPDATE and DELETE to maintain another table with the id's separated as rows. Then you can create your view using that table.

I know you said you don't mind about the speed of the query but at least that way you are not parsing all the strings every time you query the data.

parsing comma seperated values in MS-SQL (no csv or such)

2 Answers2