SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE  PathInfo 
(
    FileId	INT,
    Path	VARCHAR(512)
);

INSERT INTO  PathInfo (FileId, Path) VALUES
	('1', '\\companyName\Production\Storage\Data\Connection\106\10149\PROD\'),
	('2', '\\companyName\Production\Storage\Data\Connection\1723\3763\PROD\'),
	('3', '\\companyName\Production\Storage\Data\Connection\1534\1216\PROD\'),
	('4', '\\companyName\Production\Storage\Data\Connection\1534\NotAnId\PROD\'),
	('5', '\\companyName\Production\Storage\Data\Connection\1534\OtherPath\PROD\');

SELECT * FROM PathInfo

CREATE TABLE  Region 
(
    ID	INT,
    BusinessId	INT,
    Region	VARCHAR(512),
    Type	VARCHAR(512),
    PartnerKey	INT
);

INSERT INTO  Region (ID, BusinessId, Region, Type, PartnerKey) VALUES
	('24', '106', 'NORTH', 'NATIONAL', '23'),
	('24', '24', 'EAST', 'WORLD', '23'),
	('25', '10149', 'NORTH', 'NATIONAL', '24'),
	('26', '26', 'NORTH', 'NATIONAL', '25'),
	('27', '27', 'SOUTH', 'NATIONAL', '26'),
	('29', '29', 'NORTH', 'WORLD', '28'),
	('30', '30', 'EAST', 'WORLD', '29');

SELECT * FROM Region

CREATE TABLE PartnerInfo 
(
    PartnerKey	INT,
    Field1	VARCHAR(512),
    Field2	VARCHAR(512),
    Field3	VARCHAR(512)
);

INSERT INTO PartnerInfo (PartnerKey, Field1, Field2, Field3) VALUES
	('23', 'AAA', 'BBB', 'Alt1'),
	('24', 'DDD', 'GGG', 'Alt2'),
	('25', 'XXX', 'ZZZ', 'Alt2');

SELECT * FROM PartnerInfo

--created and run the following query which maps each FileId and Path to a FirstBusinessId and SecondBusinessId (when it is a valid integer).

DECLARE @RootLength VARCHAR(100) = '\\companyName\Production\Storage\Data\Connection\'
  
  select FileId, Path,
    TRIM('\' from 
      substring(Path,LEN(@RootLength)+1,
          charIndex('\' , substring(Path,LEN(@RootLength)+1,LEN(@RootLength)))
          )) 
        as FirstBusinessId,

substring(
    TRIM('\' from
    substring(Path,LEN(@RootLength)+LEN(substring(Path,LEN(@RootLength)+1,
          charIndex('\' , substring(Path,LEN(@RootLength)+1,LEN(@RootLength))
          )) ),LEN(@RootLength))), 0, charIndex('\',
          TRIM ('\' from substring(Path,LEN(@RootLength)+LEN(substring(Path,LEN(@RootLength)+1,
          charIndex('\' , substring(Path,LEN(@RootLength)+1,LEN(@RootLength))
          )) ),LEN(@RootLength)))
          ))
        as SecondBusinessId
 into #BusinessIDTemp -- temp table target
  from PathInfo

SELECT b.FileId, b.path, region.PartnerKey, p.Field1, p.Field2, p.Field3
  FROM #BusinessIDTemp b
INNER JOIN Region ON 
           ( TRY_CONVERT(int, SecondBusinessID) IS NOT NULL AND  SecondBusinessID=Region.BusinessID)
            OR
           ( TRY_CONVERT(int, SecondBusinessID) IS NULL AND  FirstBusinessID=Region.BusinessID)
INNER JOIN PartnerInfo p ON
       REGION.PartnerKey = P.PartnerKey

FileId	Path
1	\companyName\Production\Storage\Data\Connection\106\10149\PROD\
2	\companyName\Production\Storage\Data\Connection\1723\3763\PROD\
3	\companyName\Production\Storage\Data\Connection\1534\1216\PROD\
4	\companyName\Production\Storage\Data\Connection\1534\NotAnId\PROD\
5	\companyName\Production\Storage\Data\Connection\1534\OtherPath\PROD\

ID	BusinessId	Region	Type	PartnerKey
24	106	NORTH	NATIONAL	23
24	24	EAST	WORLD	23
25	10149	NORTH	NATIONAL	24
26	26	NORTH	NATIONAL	25
27	27	SOUTH	NATIONAL	26
29	29	NORTH	WORLD	28
30	30	EAST	WORLD	29

PartnerKey	Field1	Field2	Field3
23	AAA	BBB	Alt1
24	DDD	GGG	Alt2
25	XXX	ZZZ	Alt2

FileId	path	PartnerKey	Field1	Field2	Field3
1	\companyName\Production\Storage\Data\Connection\106\10149\PROD\	24	DDD	GGG	Alt2