SQL Server Difference in dates Query AND TRIM

I've got a very tricky scenario below, if anyone can help i'd appreciate it a lot.

I have two tables:

TX_External

TX has two columns: ID & Date_Reported

TX_External has two columns: ID & Date_Received

Essentially I want to find the difference in days between Date_Received from TX_external and Date_Reported from TX table. It has to be WORKING DAYS ONLY (no weekend or bank holidays).

So my final output should look like this

Customer ID | Days_Taken

MAIN PROBLEM. My main issue is that the id's in both tables are different ever so slightly, for example:

Tx- ID= AB_123456_ABC

TX_External- ID= AB_123456

So as you can see they are similar and refer to the same transaction but the TX table ID has random extras attached after the number in the version of "_ABC" and this could be extra numbers letters brackets etc. (so AB_123456_ABC, AB_123456_E (1), AB_123456_H, etc). To add to the confusion, the ID is not one consistent size ie they are all not AB_123456 as some could be AB_12345678! So trim to the second _ and trim

How do I trim that so I can get it exactly the same as the Tx_External ID (AB_123456) and then this will let me do a match so for that exact ID i can calculate the working day taken? Can I do this all in one query?

Sorry about massive description in advance, more than happy to explain further.

Solution

   select * from tx a 
        cross apply (select case when a.ID like '%[_]%[_]%' then
       replace(a.ID, reverse(left(reverse(a.ID), charindex('_', reverse(a.ID)))), '')
        else a.ID end txstripped)b
   join TX_External c cross apply (select case when c.ID like '%[_]%[_]%' then
     replace(c.ID, reverse(left(reverse(c.ID), charindex('_', reverse(c.ID)))), '')
     else ID end txexternal_stripped)d
   on b.txstripped=d.txexternal_stripped