在SQL中使用交叉应用时，查找两个表之间的匹配值

Question

我正试图从表A列x中提取5个或更多连续数字，并将提取的数字与表格B列z匹配。如果该值存在，也可以，但如果该值不存在，则需要将其插入表B中。我已经设法从表A中提取了数字，但在尝试JOIN时遇到了问题，因为我使用的是CROSS APPLY和CASE。也许我只是不明白该怎么做。

我用来提取数字的代码：

SELECT nvt.AdditionalInformation,
CASE
WHEN M.FirstMatch > 0
THEN SUBSTRING(AdditionalInformation, M.FirstMatch-1, N.SecondMatch-1)

--check IF TPNumber EXISTS in ChangeRequests table then add ChangeRequest Id to ReportVersionChangeRequests table
ELSE NULL
END
FROM
(
SELECT ':'+nvt.AdditionalInformation+':' AdditionalInformation
FROM dbo.NSReportVtest nvt
) nvt
CROSS APPLY(VALUES(PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', ':'+nvt.AdditionalInformation+':'))) M(FirstMatch)
CROSS APPLY(VALUES(PATINDEX('%[^0-9]%', SUBSTRING(AdditionalInformation, M.FirstMatch-1, LEN(AdditionalInformation))))) N(SecondMatch)

目前这是代码的结果：

表A:

	附加信息	(无列名(
：测试结果：	NULL
：测试结果：	256985
：测试结果：	NULL
：测试结果：	NULL
：测试结果：	NULL
：测试结果：	85965

create table NSReportVtest (AdditionalInformation nvarchar(max)) -- Source table
create table NSReportVtest2 (id int identity(1,1) ,Value int) -- destination table

insert into NSReportVtest  --- creating source data 
select 'aaa256985bbb'
union all select 'aasa123456babb'
union all select 'aaga245bfbb'
union all select 'abaa54123bnbb'
union all select 'aaba654987bmbb'
union all select 'aacabybb'

insert into NSReportVtest2(Value) -- creating dest data 
select 123456
union all select 54123 

insert into NSReportVtest2  --- inserting missing data using left join 
select cast( substring(SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation)) ,1,PATINDEX('%[^0-9]%',SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation))) -1 ) as int) Value
from  NSReportVtest t1
left outer join  NSReportVtest2 t2
on cast( substring(SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation)) ,1,PATINDEX('%[^0-9]%',SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation))) -1 ) as int) = t2.Value
where t2.id is null 
and  cast( substring(SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation)) ,1,PATINDEX('%[^0-9]%',SUBSTRING(AdditionalInformation,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AdditionalInformation), LEN(AdditionalInformation))) -1 ) as int)  <>0 

我找到了一个有效的解决方案。我INSERT INTO是一个#temp表，然后我可以选择特定的列并将其连接到第二个表，这使得管理数据更容易。

SELECT nv.AdditionalInformation, 
nv.Id,
CASE
WHEN M.FirstMatch > 0
THEN SUBSTRING(AdditionalInformation, M.FirstMatch-1, N.SecondMatch-1)
ELSE NULL
END AS ExtractedTP
INTO #temp
FROM
(
SELECT ':'+nv.AdditionalInformation+':' AdditionalInformation, 
nv.Id
FROM dbo.NSReportVtest nv
) nv
CROSS APPLY(VALUES(PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', ':'+nv.AdditionalInformation+':'))) M(FirstMatch)
CROSS APPLY(VALUES(PATINDEX('%[^0-9]%', SUBSTRING(AdditionalInformation, M.FirstMatch-1, LEN(AdditionalInformation))))) N(SecondMatch);
--select and join temp table to ChangeRequests table to see which TP Numbers exist
SELECT t.Id, 
t.ExtractedTP, 
nrt.TPNumber, 
nrt.Id
FROM #temp t
LEFT JOIN dbo.NSChangeRequestsTest nrt ON t.ExtractedTP = nrt.TPNumber
ORDER BY t.ExtractedTP DESC;