SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #TEST
(
   GroupId INT NOT NULL,
   ItemId INT NOT NULL,
   Ref1 NVARCHAR(5) NULL,
   Ref2 NVARCHAR(6) NULL
)

INSERT INTO #TEST VALUES
   (1, 1, NULL, N'A123X'),
   (1, 2, N'A123', N'10999'),

   (2, 1, N'A456', N'10998'), --should not return
   (2, 2, N'A789', N'10997'), --should not return

   (3, 1, NULL, N'A0134X'),
   (3, 2, N'A0134', N'10996'),
 
   (4, 1, N'A15', N'10994'),   --should not return
   (4, 2, N'A17', N'10994'),   --should not return
 
   (5, 1, NULL, N'00108P'),   --should not return
   (5, 2, NULL, N'00109P'),   --should not return

   (6, 2, NULL, N'00107P') --should not return
   ;
11 rows affected

SELECT T.GroupId, T.ItemId, T.Ref1, T.Ref2
FROM #TEST T1
JOIN #TEST T2
    ON T2.GroupId = T1.GroupId
    AND T2.Ref1 = LEFT(T1.Ref2, LEN(T2.Ref1))
CROSS APPLY (
    SELECT T1.GroupId, T1.ItemId, T1.Ref1, T1.Ref2
    UNION ALL
    SELECT T2.GroupId, T2.ItemId, T2.Ref1, T2.Ref2
) T  
WHERE T1.Ref1 IS NULL;


GroupId
ItemId
Ref1
Ref2


1
1
null
A123X

1
2
A123
10999

3
1
null
A0134X

3
2
A0134
10996


-- Add a case with ambiguous matches
INSERT INTO #TEST
VALUES
   (7, 1, NULL, N'X1234Z'),
   (7, 2, N'X123', N'20997'),   -- Both are matching prefixes to 'X1234Z'
   (7, 3, N'X1234', N'20996');  -- Both are matching prefixes to 'X1234Z'
3 rows affected

SELECT DISTINCT T.GroupId, T.ItemId, T.Ref1, T.Ref2
FROM #TEST T1
JOIN #TEST T2
    ON T2.GroupId = T1.GroupId
    AND T2.Ref1 = LEFT(T1.Ref2, LEN(T2.Ref1))
CROSS APPLY (
    SELECT T1.GroupId, T1.ItemId, T1.Ref1, T1.Ref2
    UNION ALL
    SELECT T2.GroupId, T2.ItemId, T2.Ref1, T2.Ref2
) T  
WHERE T1.Ref1 IS NULL;


GroupId
ItemId
Ref1
Ref2


1
1
null
A123X

1
2
A123
10999

3
1
null
A0134X

3
2
A0134
10996

7
1
null
X1234Z

7
2
X123
20997

7
3
X1234
20996


-- Only select a Ref1 value that matches 
SELECT DISTINCT T.GroupId, T.ItemId, T.Ref1, T.Ref2
FROM #TEST T1
JOIN #TEST T2
    ON T2.GroupId = T1.GroupId
    AND T1.Ref2 LIKE T2.Ref1 + '[A-Z]'
CROSS APPLY (
    SELECT T1.GroupId, T1.ItemId, T1.Ref1, T1.Ref2
    UNION ALL
    SELECT T2.GroupId, T2.ItemId, T2.Ref1, T2.Ref2
) T  
WHERE T1.Ref1 IS NULL;


GroupId
ItemId
Ref1
Ref2


1
1
null
A123X

1
2
A123
10999

3
1
null
A0134X

3
2
A0134
10996

7
1
null
X1234Z

7
3
X1234
20996


-- Another approach is to select the longest Ref1 value that matches
SELECT DISTINCT T.GroupId, T.ItemId, T.Ref1, T.Ref2
FROM #TEST T1
CROSS APPLY (
    -- Select at most one best match
    SELECT TOP 1 T2.*
    FROM #TEST T2
    WHERE T2.GroupId = T1.GroupId
    AND T2.Ref1 = LEFT(T1.Ref2, LEN(T2.Ref1))
    ORDER BY LEN(T2.Ref1) DESC  -- Prefer longest
) T2
CROSS APPLY (
    SELECT T1.GroupId, T1.ItemId, T1.Ref1, T1.Ref2
    UNION ALL
    SELECT T2.GroupId, T2.ItemId, T2.Ref1, T2.Ref2
) T  
WHERE T1.Ref1 IS NULL;


GroupId
ItemId
Ref1
Ref2


1
1
null
A123X

1
2
A123
10999

3
1
null
A0134X

3
2
A0134
10996

7
1
null
X1234Z

7
3
X1234
20996
GroupId	ItemId	Ref1	Ref2
1	1	null	A123X
1	2	A123	10999
3	1	null	A0134X
3	2	A0134	10996
7	1	null	X1234Z
7	2	X123	20997
7	3	X1234	20996