Split column with delimited values for an inner join

Question

I need to perform an inner join to a column containing delimited values like:

123;124;125;12;3433;35343;

Now what I am currently doing is this:

ALTER procedure [dbo].[GetFruitDetails]
(
    @CrateID int
)
AS

SELECT Fruits.*, Fruits_Crates.CrateID 
    FROM Fruits_Crates INNER JOIN Fruits 
    ON Fruits_Crates.FruitID = Fruits.ID
    WHERE Fruits_Crates.CrateID = @CrateID

Now the issue is I am saving the data this way:

FruitCrateID  FruitID 
1             1;
2             1;2;3;4
3             3;

How can I inner join FruitsIDs to the fruit table to get fruit details as well?

The actual problem is that you store multiple values in a single column which you should never do. Can you cnahge you table design? — juergen d, Nov 10 '14 at 11:18
@juergend yes I can, but then I have another problem which is described here, http://stackoverflow.com/questions/25869110/stored-procedure-insert-multiple-rows — Mathematics, Nov 10 '14 at 11:19
You can try something like ... @multi_value LIKE '%;'+FruitID+';%'. For parentheses you can add combinations '(%'+... or simply get values in other format e.g. ";123;124;125;12;". However, it is better to change DB design. — i486, Nov 10 '14 at 11:24
possible duplicate of [SQL Server : split value based on delimiter and match at run-time](http://stackoverflow.com/questions/14418005/sql-server-split-value-based-on-delimiter-and-match-at-run-time) — jpw, Nov 10 '14 at 11:24
@CustomizedName The answer in the post I flagged as duplicate should work for you, although changing the design as _juergen d_ suggested might be a better idea. — jpw, Nov 10 '14 at 11:25

score 1 · Accepted Answer · edited May 23 '17 at 10:29

Using the method posted in this answer, you can convert the delimited string into rows of a temp table and then join to that:

SQL Fiddle

Schema Setup:

CREATE TABLE Fruits_Crates
    ([FruitCrateID] int, [FruitID] varchar(10))
;

INSERT INTO Fruits_Crates
    ([FruitCrateID], [FruitID])
VALUES
    (1, '1;'),
    (2, '1;2;3;4;'),
    (3, '3;')
;

CREATE TABLE Fruits
    ([FruitID] int, [FruitName] varchar(10))
;

INSERT INTO Fruits
    ([FruitID], [FruitName])
VALUES
    (1, 'Apple'),
    (2, 'Banana'),
    (3, 'Orange'),
    (4, 'Pear')
;

Insert to temp table:

 SELECT A.[FruitCrateID],  
     Split.a.value('.', 'VARCHAR(100)') AS FruitId  
 INTO #fruits
 FROM  (SELECT [FruitCrateID],  
         CAST ('<M>' + REPLACE([FruitID], ';', '</M><M>') + '</M>' AS XML) AS String  
     FROM  Fruits_Crates) AS A CROSS APPLY String.nodes ('/M') AS Split(a)

Join temp table to lookup:

SELECT t1.*, t2.FruitName
FROM #Fruits t1
INNER JOIN Fruits t2 on t1.FruitId = t2.FruitId

Results:

| FRUITCRATEID | FRUITID | FRUITNAME |
|--------------|---------|-----------|
|            1 |       1 |     Apple |
|            2 |       1 |     Apple |
|            2 |       2 |    Banana |
|            2 |       3 |    Orange |
|            2 |       4 |      Pear |
|            3 |       3 |    Orange |

I am going to try changing database design and then using c# table valued parameter instead, but I think your answer is right too, thank you :) — Mathematics, Nov 10 '14 at 11:47
@CustomizedName yeah, changing the design is the correct thing to do. — Tanner, Nov 10 '14 at 11:47

Split column with delimited values for an inner join

1 Answers1

SQL Fiddle