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!
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.