use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.A
(
 N int not null, DELETED bit not null
);

insert into dbo.A
 (N, DELETED)
 select
  a.N, a.DELETED
 from
  (select top (1000) 1 from master.dbo.spt_values a cross join master.dbo.spt_values b) t(dummy) cross apply
  (
   SELECT 1 AS N, 0 AS DELETED UNION ALL
   SELECT 2 AS N, 0 AS DELETED UNION ALL
   SELECT 3 AS N, 1 AS DELETED UNION ALL
   SELECT 4 AS N, 0 AS DELETED UNION ALL
   SELECT 5 AS N, 0 AS DELETED
  ) a(N, DELETED);

create index IX_A__N on dbo.A(N) include (DELETED);

create table dbo.B
(
 S varchar(1000), ID varchar(100) --primary key
);

insert into dbo.B
 (ID, S)
select
 concat(b.ID, t.n), b.S
from
 (select top (1000) row_number() over (order by 1/0) from master.dbo.spt_values a cross join master.dbo.spt_values b) t(n) cross apply
 (
  SELECT 'dadasdas X=1;X=5;X=8; adad' AS S, 'A' AS ID UNION ALL
  SELECT 'adas X=3;X=2;X=2;X=7 dddd;' AS S, 'B' AS ID UNION ALL
  SELECT 'ada423s X=6;X=3 dddd;' AS S, 'C' AS ID UNION ALL
  SELECT 'ada423s X=1;X=2 dddd;' AS S, 'D' AS ID
 ) b
go

declare @N int, @ID varchar(100);

set statistics xml, time, io on;

SELECT @N = C.N, @ID = B.ID FROM dbo.B
OUTER APPLY
(
SELECT TOP 1 A.N FROM dbo.A
WHERE
B.S LIKE CONCAT('%X=', A.N, ';%') AND
A.DELETED = 0
) C;

print '';
print '------------------------------------------------------------------------------';
print '';

SELECT @N = C.N, @ID = B.ID FROM dbo.B
OUTER APPLY
(
SELECT TOP (1) t.N FROM
(
  select
   cast(replace(value, 'X=', '') as int)
  from
   string_split(replace(B.S, ';', ' '), ' ')
  where
   value like 'X=[0123456789]%'
) t(N) join
dbo.A on A.N = t.N and A.DELETED = 0
) C;

set statistics xml, time, io off;
go

drop table dbo.A, dbo.B;
go

(5000 rows affected)

(4000 rows affected)
Table 'A'. Scan count 1, logical reads 12000, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'B'. Scan count 1, logical reads 21, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 6203 ms,  elapsed time = 6379 ms.
 

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
------------------------------------------------------------------------------

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'A'. Scan count 6, logical reads 20, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'B'. Scan count 1, logical reads 21, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 53 ms.