SQL Join with partial string match-CodePudding

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

CodePudding user response：

Try this:

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

CodePudding user response：

You can use LIKE with CONCAT to build the expression, see this fiddle:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d7bb4781488e53c31abce0f38d0aaef4

SELECT *
FROM TableA TA
JOIN TableB TB on (
  TA.types = TB.types AND
  TB.subgroup LIKE concat('XX-', TA.groups, '-XX%')
);

I am dumping all the returned data so you can see it, but you would want to modify that to return only DISTINCT TA.id.

It is possible to build the expression using instead of CONCAT:

'XX-'   TA.groups   '-XX%'

Also, I would very strongly warn you against using column names that are reserved words. Both GROUP and TYPE are in use by the SQL engine, so to use them you would have to escape them every time, and they might also cause confusion to anybody reading or maintaining your code.