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

```