SQL Join with partial string match

I have a table ‘TableA’ like:

ID Group         Type       
1  AB            SomeValue 
2  BC            SomeValue   

Another table ‘TableB’ like:

Product Subgroup             Type
A       XX-AB-XX-text        SomeValue
B       XX-BC-XY-text        SomeValue

I am using INNER JOIN between two tables like:

SELECT DISTINCT ID
FROM TableA TA
INNER JOIN TableB TB
ON TA.Type=TB.Type

I want to add another condition for join, which looks for value of ‘Group’ in ‘Subgroup’ and only joins if the ‘Group’ Value matches after ‘XX-‘ and before ‘-XX’.

In other words, join only if Group ‘AB’ shows up at the correct place in Subgroup column.

How can I achieve this? I am using MSSQL

Answer

Try this:

SELECT (DISTINCT ID) 
FROM TableA TA
INNER JOIN TableB TB
ON TA.Type=TB.Type AND TB.SubGroup LIKE '__-' + TA.Group + '%'