CODEWITHSUNDEEP
sqlsql-server
Thelonious Monk
Thelonious Monk

Reputation: 466

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

Upvotes: 1

Views: 2828

Answers (2)

Chris Strickland
Chris Strickland

Reputation: 3490

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.

Upvotes: 1

James Anderbard
James Anderbard

Reputation: 504

Try this:

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

Upvotes: 1

Related Questions