How to use Substring in left outer join

user_17698961 0
Viewed 442

I tried to join these 2 tables with ALTERNATE_ID's using substring in left outer join but it doesn't work. Can someone suggest me how to do it. Here Table1 is master table,

SELECT
tab1.USER_NAME,
tab2.ALTERNATE_ID as 'Contract_no'

FROM TABLE1 tab1

LEFT OUTER JOIN TABLE2 tab2 ON Table2.ALTERNATE_ID = SUBSTRING(tab1.ALTERNATE_ID,0,CHARINDEX('/',tab1.ALTERNATE_ID)

TABLE1:

ALTERNATE_ID 100-0000053-001/0001

TABLE2

ALTERNATE_ID 100-0000053-001
left-join sql
4 Answers

You have to use substring from first char to position of \ - 1 But if you have to join based on part of column, then you should created index on the substring part.

SELECT
tab1.USER_NAME,
tab2.ALTERNATE_ID as 'Contract_no'
FROM TABLE1 tab1
LEFT OUTER JOIN TABLE2 tab2 
ON Table2.ALTERNATE_ID 
= 
SUBSTRING(tab1.ALTERNATE_ID,1,CHARINDEX('/',tab1.ALTERNATE_ID-1)
user_159986610

You can also use SUBSTRING_INDEX to split the string like this:

SELECT tab1.USER_NAME, tab2.ALTERNATE_ID as 'Contract_no'
FROM TABLE1 tab1
LEFT OUTER JOIN TABLE2 tab2 
ON Table2.ALTERNATE_ID = SUBSTRING_INDEX(tab1.ALTERNATE_ID, "/", 1);
user_52472790

You can use OUTER APPLY in the following way:

SELECT
tab1.USER_NAME,
tab2.ALTERNATE_ID as 'Contract_no'
FROM TABLE1 tab1
OUTER APPLY (Select ALTERNATE_ID 
             From TABLE2 
             Where ALTERNATE_ID=SUBSTRING(tab1.ALTERNATE_ID,1,CHARINDEX('/',tab1.ALTERNATE_ID)-1)) As tab2
user_117698590

If you use PATINDEX, then it's easy to get the position of the digit before the slash.

SELECT *
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 
  ON t2.ALTERNATE_ID = LEFT(t1.ALTERNATE_ID, PATINDEX('%[0-9]/%', t1.ALTERNATE_ID))

Or looking at it differently, the one in Table1 is like the one in Table2

SELECT *
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 
  ON t1.ALTERNATE_ID LIKE t2.ALTERNATE_ID+'/%'

Demo on db<>fiddle here

user_40034190
Related