Join table on substring-ed values

Question

So basically, I have two table, X and Y. I want to join them with the substring-ed value but I'm unsure of how I can do so.

Sample code of X:

select distinct
    trim(ws.WS_name) as [WS Name],
    substring(trim(e.equip_id),1,8) as [Equid ID], 
into #eqm_hist
from eqmhist

Sample table of X:

WS name	Equid ID
WS 21	KTRGAF7L
WS 21	KTRGAF7L

Sample code of Y:

select distinct
    [Batch ID],
    [Qty],
    rtrim(flh.tracking_interface_id) as [Tool],
    [WS Name],
into #main 
from flh

Sample table of Y:

Batch ID	Qty	Tool
12345	100	KTRGAF7L00
23456	100	KTRGAF7L00

Do note that [Tool] and [Equid ID] is the same

How can I join them together using the substring-ed values to achieve the table like below? Do I also have to remove the last two digit for [Tool] in table Y to do so?

Batch ID	Qty	Tool	WS Name
12345	100	KTRGAF7L00	WS 21
23456	100	KTRGAF7L00	WS 21

T N · Accepted Answer · 2023-02-06T06:26:44.603

Per the first answer to this question, it appears that SQL Server will optimize a LIKE 'prefix%' condition to use an index.

So if you have an index on Y(Tool), you should be able to efficiently run the query:

SELECT *
FROM X
JOIN Y ON Y.Tool LIKE X.[Equid ID] + '%'

Follow-up note: If [Equid ID] values may have variable length, but the tool values will always have exactly two extra characters, you should change join condition to ON Y.Tool LIKE X.[Equid ID] + '__'. Underscores are single-character wildcards. This will handle vases where you have [Equid ID] values "Thing" and "ThingA" with Tool values "Thing01", "Thing02", "ThingA01", and "ThingA02".

Join table on substring-ed values

1 Answers1