SQL Server 2022

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

CREATE TABLE table_name
(
  [Statement of Work ID]     varchar(20)  NULL,
  [Primary Cost Center Code] varchar(20)  NULL,
  [Purchase Order Number]    varchar(20)  NULL,
  [Invoice ID]               varchar(20)  NULL,
  [Project ID]               varchar(100) NULL
);
INSERT INTO table_name 
VALUES
  ('SOW111', 'CC111', 'PO111', 'INV111', 'Project number 1000000111.'),
  ('SOW222', 'CC222', 'PO222', 'INV222', '1000000200 proj num'),
  ('SOW222', 'CC222', 'PO222', 'INV222', '1000000222 proj num'),
  ('SOW333', 'CC333', 'PO333', 'INV333', 'P# 1000000300'),
  ('SOW333', 'CC333', 'PO333', 'INV333', 'P# 1000000333'),
  ('SOW333', 'CC333', 'PO333', 'INV333', 'P# 1000000300 or maybe 1000000999'),
  ('SOW444', 'CC444', 'PO444', 'INV444', '1000000444'),
  ('SOW555', 'CC555', 'PO555', 'INV555', ''),
  ('SOW666', 'CC666', 'PO666', 'INV666', ''),
  ('SOW666', 'CC666', 'PO666', 'INV666', NULL),
  ('SOW777', 'CC777', 'PO777', 'INV777', NULL)
;
11 rows affected

SELECT 
  projects.[Statement of Work ID],
  projects.[Primary Cost Center Code],
  projects.[Purchase Order Number],
  projects.[Invoice ID],
  pid.[Project ID]
FROM 
(
  SELECT DISTINCT
    ISNULL([Statement of Work ID],'') AS [Statement of Work ID],
    ISNULL([Primary Cost Center Code],'') AS [Primary Cost Center Code],
    ISNULL([Purchase Order Number],'') AS [Purchase Order Number],
    ISNULL([Invoice ID],'') AS [Invoice ID]
  FROM table_name
) projects
OUTER APPLY
(
  SELECT TOP(1) 
    SUBSTRING([Project ID], PATINDEX('%[1-1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', [Project ID]), 10) AS [Project ID]
  FROM table_name t
  WHERE 1=1
    AND ISNULL(t.[Statement of Work ID],'') = projects.[Statement of Work ID]
    AND ISNULL(t.[Primary Cost Center Code],'') = projects.[Primary Cost Center Code]
    AND ISNULL(t.[Purchase Order Number],'') = projects.[Purchase Order Number]
    AND ISNULL(t.[Invoice ID],'') = projects.[Invoice ID]
    AND t.[Project ID] LIKE '%[1-1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
  ORDER BY [Project ID] DESC
) pid


Statement of Work ID
Primary Cost Center Code
Purchase Order Number
Invoice ID
Project ID


SOW111
CC111
PO111
INV111
1000000111

SOW222
CC222
PO222
INV222
1000000222

SOW333
CC333
PO333
INV333
1000000333

SOW444
CC444
PO444
INV444
1000000444

SOW555
CC555
PO555
INV555
null

SOW666
CC666
PO666
INV666
null

SOW777
CC777
PO777
INV777
null

Statement of Work ID	Primary Cost Center Code	Purchase Order Number	Invoice ID	Project ID
SOW111	CC111	PO111	INV111	1000000111
SOW222	CC222	PO222	INV222	1000000222
SOW333	CC333	PO333	INV333	1000000333
SOW444	CC444	PO444	INV444	1000000444
SOW555	CC555	PO555	INV555	null
SOW666	CC666	PO666	INV666	null
SOW777	CC777	PO777	INV777	null