title: SQL Server - Join Table1 to Table2 ON substring(T1.Field) = T2.ID
body: <p>I have two tables: MyOrders and MyDrivers.</p>
  
  <p>In table MyOrders, I have a column called Details (datatype TEXT - I know, but I didn't build the database...)</p>
  
  <p>In MyOrders.Details, there are sometimes comma-separated lists of numerical values which correspond to the ID values of table MyDrivers. </p>
  
  <p>The goal is to join MyOrders to MyDrivers using these lists. </p>
  
  <p>For example: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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...</p>
  
  <p>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.  </p>
  
  <p>Thank you very much in advance!</p>