SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Table1 (
ALTERNATE_ID varchar(30)
);

create table  Table2 (
ALTERNATE_ID varchar(30)
);

insert into Table1 values (
'100-0000053-001/0001' 
)

insert into Table2 values (
'100-0000053-001' 
)
2 rows affected

select *
, left(ALTERNATE_ID, patindex('%[0-9]/%',ALTERNATE_ID))
from Table1


ALTERNATE_ID
(No column name)


100-0000053-001/0001
100-0000053-001


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


ALTERNATE_ID
ALTERNATE_ID


100-0000053-001/0001
100-0000053-001


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


ALTERNATE_ID
ALTERNATE_ID


100-0000053-001/0001
100-0000053-001