Find matching values between two tables when using cross apply in SQL

Question

I am trying to extract 5 or more consecutive numbers from from Table A Column x and match the extracted numbers to Table B column z. If the value exists, that is fine but if the value does not exist it needs to be inserted into Table B. I have managed to extract the numbers from Table A but I am stuck when trying to JOIN because I am using CROSS APPLY and CASE. Perhaps I am just not understanding how to do this.

Code I am using to extract the numbers:

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)

At the moment this is the result of the code:

Table A:

AdditionalInformation	(No Column name)
:Test Results:	NULL
:Test Results:	256985
:Test Results:	NULL
:Test Results:	NULL
:Test Results:	NULL
:Test Results:	85965

Expected Results:

Table A:

AdditionalInformation	(No Column name)
:Test Results:	NULL
:Test Results:	256985
:Test Results:	NULL
:Test Results:	NULL
:Test Results:	NULL
:Test Results:	85965

Table B:

Id	Number
1	61758
2	85965
3	56456
4	78945

Expected Output after Join

Table C:

Id	Number
1	61758
2	85965
6	56456
8	78945
9	256985 (Added entry)

I'm lost. A query returns one result set. Do you want multiple result sets? Do you want to modify table b? Do you want the results in a new result set? How is `id` calculated? — Gordon Linoff, Jan 04 '21 at 13:03
Apologies for it being a bit confusing. I need one result set. The `id` column is auto-generated. I need Table A and Table B basically to merge into Table C. The provided code currently produces result set Table A. — N101Seggwaye, Jan 04 '21 at 13:08
. . Why are the `id`s changing then? Why not just use the ids for `b`? — Gordon Linoff, Jan 04 '21 at 13:48
Aside: `ELSE NULL` is not necessary in a `CASE`, although it does make it a little clearer — Charlieface, Jan 04 '21 at 14:53
@GordonLinoff The `id`'s change because some records were deleted and the table is not reseeded. I am not focusing on the `id`'s because all I want to do is `join` on the Number column. I can't use the `id`'s for Table B because I don't manually add it and it would anyway create conflicts because of duplications — N101Seggwaye, Jan 05 '21 at 07:46

score 0 · Answer 1 · answered Jan 04 '21 at 15:44

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

score 0 · Accepted Answer · answered Jan 05 '21 at 07:43

I found a solution that works. I INSERT INTO a #temp table which then I can select specific columns and join that to a second table which makes it easier to manage data.

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;

Find matching values between two tables when using cross apply in SQL

2 Answers2