Split string in cell and create multiple rows

Question

I've got an old database dropped in my knee and it's not very well modeled. The crappy modelling is taken care of in an overly complex software application, but I need to work more closely to the database.

Instead of understanding how foreign keys and relations work, the database is modeled like this:

Table1 contains ID_1, Value
Table2 contains ID_2, AnotherValue, ID_1s

where ID_1 is an INT and ID_1s is a varchar containing a pipe-separated string of references to ID_1 (i.e. '1|4|5', meaning it is referenced to rows where table1.ID_1 is 1, 4, or 5)

Now, I need to join these two tables rather than have an application parse through them and link them programatically. At the same time, I can't modify the db-model itself since it will break the application.

Bsaically, I need to emulate a many-to-many relation with what I've got.

Does anyone have an idea of a somewhat solid way of doing this?

(In the future, there is a plan to basically redesign the database and use views as an abstraction for the application.)

What about creating a table function that splits the varchar by "|" character and perform the join from this function? — Ivan Sivak
– Ivan Sivak, Commented Dec 30, 2014 at 13:56
Are you developing new code which will use your new virtual many-to-many relation? If so, what are restrictions in changing your data model? Why it is a problem to add new view? — Serg
– Serg, Commented Dec 30, 2014 at 14:31
It's an ad-hoc query, but I will probably need it more than once in the near future. The long-term plan is to to a complete rewrite, though. — CB Du Rietz
– CB Du Rietz, Commented Dec 30, 2014 at 15:01
A problem with views to mitigate the problem is that the underlying database is inconsistent due to the bad db design... — CB Du Rietz
– CB Du Rietz, Commented Dec 30, 2014 at 15:12

Community · Accepted Answer · 2017-05-23 12:20:34Z

1

While it isn't going to be the fastest of queries, you can apply the same idea as outlined here: Parameterize an SQL IN clause

SELECT Table1.Value
FROM Table1
INNER JOIN Table2
    ON '|' + Table2.ID_1s + '|' LIKE '%|' + CAST(Table1.ID_1 AS varchar(20)) + '|%'

-- Add any other conditions in a WHERE clause.

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Dec 30, 2014 at 14:00

Jason Faulkner

6,6682 gold badges31 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2941651 · Accepted Answer · 2014-12-30 13:58:00Z

0

I think you could use something like this

SELECT 
  Table1.*,
  Table2.*
FROM
  Table1
  INNER JOIN Table2
    ON PATINDEX('%|'+LTRIM(RTRIM(CONVERT(NVARCHAR(100), Table1.ID_1)))+'|%', Table2.ID_1s) <> 0

answered Dec 30, 2014 at 13:58

user2941651

Collectives™ on Stack Overflow

Split string in cell and create multiple rows

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related