SQL Server - Concatenate Tables 1 to Table 2. Substring (T1.Field) = T2.ID
I have two tables, MyOrders and MyDrivers.
In the MyOrders table, I have a column named Details (datatype TEXT - I know, but I haven't created a database ...)
MyOrders.Details has sometimes comma separated lists of numeric values that correspond to the ID values of the MyDrivers table.
The goal is to join MyOrders in 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 is that for MyOrderID 1, I would get 5 rows in the result: one for each of the five drivers assigned to that order in the parts list of the same order. If there is no list (NULL, '', '', ''), then I don't want the returned rows to be returned. ** Sometimes users remove the values in this field and leave spaces behind, so I guess I will have to use TRIM. But they always add the required comma, so at least there is that ...
I have no idea how to do this; I still have a lot to learn in SQL. Any helpful advice / ideas would be greatly appreciated.
Thank you in advance!
source to share
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 it IN
expects a list of values, not a single comma-separated string value.
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 script provided by wewesthemenace in his answer , I tested his proposed solution (split string) against my proposed solution (for example) for performance. it seems like using methods like this is much faster (less than half the time) for your sample data (these could be different results if the data is different). you can check it in this link .
If possible, I would suggest strong to change the structure of the database and create another table to hold the values that are currently stored in the Details column.
source to share
First you need a splitter function to split your comma separated values. Here is a function DelmitedSplitN4K
written by Jeff Moden for one of the fastest splitters out there. 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 the values into first MyOrders.Details
. After splitting you will do 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
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 |
source to share