Joining tables with a substring before a character

Question

I am trying to join tables. The joining column is a contract number. The table I am trying to join it to has multiple contract numbers, each seperated by commas (ie. Contract1,Contract2,Contract3).

I have tried using a LEFT CHARINDEX, and a SUBSTRING CHARINDEX, but none are working.

The join I have tried doesn't seem to work:

SELECT *
FROM  tblA AS C WITH (NOLOCK)
INNER JOIN tblB AS tmp WITH(NOLOCK) 
      ON CONVERT(VARCHAR, c.Contract_No) = 
         CONVERT(VARCHAR, LEFT(tmp.ContractNo, CHARINDEX('-', tmp.ContractNo) - 1))

Show us some sample table data and the expected result - as formatted text, not images. Simplify if needed, [mcve]. — jarlh, Jan 30 '20 at 10:08
Never store data as comma separated values, it will only cause you lots of trouble. — jarlh, Jan 30 '20 at 10:09

score 2 · Answer 1 · answered Jan 30 '20 at 10:14

A typical method is to search for a string in a CSV list is to use the following condition:

',' + tmp.ListOfContract_No + ',' like '%,' + c.Contract_No + ',%'

From your existing query it is unclear which column contains the list of contract numbers and which contains the single contrat, so I renamed the columns to be more talkative. I also do not see the need to convert to varchar - as far as concerned both columns should be of that datatype already of they contain values like 'Contract1'.

Please note that this method is both inefficient and obsfucated. You really should be fixing your schema to store each item of the CSV list in a separate table row. Storing CSV lists in columns of a relational database is a bad practice and the root of many evils. More on this in this famous SO post (it relates to MySQL but would equally apply to SQL Server).

score 0 · Accepted Answer · answered Jan 30 '20 at 10:39

0

A way to join which I have figured out is

SELECT *
FROM  tblA AS C WITH (NOLOCK)
INNER JOIN tblB AS tmp WITH(NOLOCK) 
      ON CONVERT(VARCHAR, c.Contract_No) = 
SUBSTRING(tmp.ContractNo,0,CHARINDEX(',',tmp.ContractNo,0))

answered Jan 30 '20 at 10:39

Onga Leo-Yoda Vellem

296
3
10

Joining tables with a substring before a character

2 Answers2