SQL Server - Join Table1 to Table2 ON substring(T1.Field) = T2.ID

Question

I have two tables: MyOrders and MyDrivers.

In table MyOrders, I have a column called Details (datatype TEXT - I know, but I didn't build the database...)

In MyOrders.Details, there are sometimes comma-separated lists of numerical values which correspond to the ID values of table MyDrivers.

The goal is to join MyOrders to MyDrivers using these lists.

For example:

CREATE TABLE MyOrders 
(
MyOrderID INT IDENTITY,
Details TEXT -- Wish it were NVarchar, but what can I do...
)
GO

CREATE TABLE MyDrivers
(
MyDriverID INT IDENTITY,
DriverName NVARCHAR(50)
)
GO

INSERT INTO MyOrders (Details) VALUES ('1,3,5,7,9')
INSERT INTO MyOrders (Details) VALUES ('2,4,6,8')
INSERT INTO MyOrders (Details) VALUES ('1,2,3,4')
INSERT INTO MyOrders (Details) VALUES ('4,5,6,7,8')
INSERT INTO MyOrders (Details) VALUES (NULL)
INSERT INTO MyOrders (Details) VALUES ('')
INSERT INTO MyOrders (Details) VALUES ('9')

INSERT INTO MyDrivers (DriverName) VALUES ('Alex')
INSERT INTO MyDrivers (DriverName) VALUES ('Bobby')
INSERT INTO MyDrivers (DriverName) VALUES ('Carl')
INSERT INTO MyDrivers (DriverName) VALUES ('Daryl')
INSERT INTO MyDrivers (DriverName) VALUES ('Ed')
INSERT INTO MyDrivers (DriverName) VALUES ('Frank')
INSERT INTO MyDrivers (DriverName) VALUES ('George')
INSERT INTO MyDrivers (DriverName) VALUES ('Hal')
INSERT INTO MyDrivers (DriverName) VALUES ('Ichabod')
INSERT INTO MyDrivers (DriverName) VALUES ('Justin Timberlake')

SELECT * FROM MyOrders O
INNER JOIN MyDrivers D
    ON D.MyDriverID = ...? substring()? patindex()?
WHERE O.MyOrderID = 1

The desired result here being that for MyOrderID 1, I would receive 5 rows as a result: One for each of the five drivers assigned to this order in the Details list of that same Order. If there is no list (NULL, '', ' ', ' ') then I don't want any rows returned. **Sometimes users delete the values in this field and leave spaces behind, so I'm assuming I'll have to use TRIM. But they do always add the necessary comma, so at least there's that...

I have no idea how to go about this; I still have a lot to learn in SQL. Any helpful tips/ideas would be greatly appreciated.

Thank you very much in advance!

Answer 1

You can use IN like this:

SELECT * 
FROM MyOrders O
INNER JOIN MyDrivers D
ON ',' + CAST(D.MyDriverID as varchar) +',' IN(','+ ISNULL(O.Details, '')  +',')
WHERE O.MyOrderID = 1

Update
Actually, you can't use IN, but you can use LIKE. the reason for this is that IN expects a list of values and not a single string value separated by commas.

SELECT MyOrderID, MyDriverID, DriverName
FROM MyOrders O
INNER JOIN MyDrivers D
ON ','+ cast(ISNULL(O.Details, '') as varchar(max))  +',' LIKE 
   '%,' + CAST(D.MyDriverID as varchar) +',%'
WHERE O.MyOrderID = 1

Using the fiddle provided by wewesthemenace in his answer, I've tested his suggested solution (split string) vs my suggested solution (like) for performance. it seems that using like is much faster (less then half of the time) for your sample data (it could be different results if the data is different). you can check it yourself in this link.

If possible, I would strongly suggest to change the database structure and create another table to hold the values that are currently stored in the Details column.

Answer 2

First, you need a splitter function to split your comma-separated values. Here is the DelmitedSplitN4K function written by Jeff Moden for one of the fastest splitter there is. Read this article for more information.

CREATE FUNCTION [dbo].[DelimitedSplitN4K](
    @pString NVARCHAR(4000), 
    @pDelimiter NCHAR(1)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
cteTally(N) AS(
    SELECT TOP (ISNULL(DATALENGTH(@pString)/2,0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (
    SELECT 1 UNION ALL 
    SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(
    SELECT s.N1,
        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,4000)
    FROM cteStart s
)
SELECT 
    ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
    Item       = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;

Then, you want to split first the values in MyOrders.Details. After splitting, you the perform a JOIN to achieve the desired result:

WITH CteSplitted AS(
    SELECT
        mo.MyOrderID,
        CAST(s.Item AS INT) AS DriverID
    FROM MyOrders mo
    CROSS APPLY dbo.DelimitedSplitN4K(CONVERT(NVARCHAR(4000), mo.Details), ',') s
)
SELECT * 
FROM CteSplitted cs
INNER JOIN MyDrivers d
    ON d.MyDriverID = cs.DriverID
WHERE cs.MyOrderID = 1

SQL Fiddle

Result

| MyOrderID | DriverID | MyDriverID | DriverName |
|-----------|----------|------------|------------|
|         1 |        1 |          1 |       Alex |
|         1 |        3 |          3 |       Carl |
|         1 |        5 |          5 |         Ed |
|         1 |        7 |          7 |     George |
|         1 |        9 |          9 |    Ichabod |