Find differences between two strings in SQL

Question

I want to find the difference between two strings and string manipulation isn't my strong suit. I have the following code.

SELECT 
T0.[Code], 
T0.[U_CardCode],
T1.CardName, 
T0.[U_Subj], 
CASE WHEN T0.[U_SubStatus] = 'o' THEN 'Open' ELSE 'Closed' END AS 'Status', 
CONCAT(T0.[U_Every],' ' ,CASE WHEN T0.[U_Unit] = 3 THEN 'Months' ELSE 'Year' END) AS 'Frequency', 
T0.[U_FDate], 
T0.[U_CatCode], 
T0.[U_UserField1] AS 'Expected Hours', 
T0.[U_Rem],
(SELECT TOP 1 (len(CAST(A.U_ACEqCodes AS VARCHAR(MAX))) - len(replace(CAST(A.U_ACEqCodes AS VARCHAR(MAX)), ';', ''))) + 1 FROM [dbo].[@SWA_CT_SUBSCR_PLTPL]  A INNER JOIN [dbo].[@SWA_CT_SUBSCR]  B ON B.Code = A.U_SubscriptionCode WHERE B.[U_CardCode] = T0.U_CardCode AND CONVERT(NVARCHAR(MAX),A.[U_ACEqCodes]) <> '') AS 'QTY A',

(SELECT TOP 1 A.[U_ACEqCodes] FROM [dbo].[@SWA_CT_SUBSCR_PLTPL]  A INNER JOIN [dbo].[@SWA_CT_SUBSCR]  B ON B.Code = A.U_SubscriptionCode WHERE B.[U_CardCode] = T0.U_CardCode AND CONVERT(NVARCHAR(MAX),A.[U_ACEqCodes]) <> '') AS 'Equipment in Subscription',
 
(SELECT count (A.insID) FROM OINS A WHERE A.customer = T0.U_CardCode AND (A.ItemName NOT LIKE '%%ENVIRO%%' AND A.ItemName NOT LIKE '%%Heat Seal%%' AND A.ItemName NOT LIKE '%%Printer%%' AND A.ItemName NOT LIKE '%%Commercial%%')) AS 'QTY B',

stuff((select '; '  + CAST(r.insID AS VARCHAR(100))
 from OINS r  where r.customer = T0.U_CardCode AND (r.ItemName NOT LIKE '%%ENVIRO%%' AND r.ItemName NOT LIKE '%%Heat Seal%%' AND r.ItemName NOT LIKE '%%Printer%%' AND r.ItemName NOT LIKE '%%Commercial%%')
  FOR XML PATH('')), 1, 2, '') AS [Equipment from Cards],

T0.[U_Total], 

CASE WHEN T2.GroupName NOT IN ('Customers','Retail') THEN ((SELECT CAST(count(A.insID) AS DECIMAL) FROM OINS A WHERE A.customer = T0.U_CardCode AND (A.ItemName NOT LIKE '%%ENVIRO%%' AND A.ItemName NOT LIKE '%%Heat Seal%%' AND A.ItemName NOT LIKE '%%Printer%%' AND A.ItemName NOT LIKE '%%Commercial%%')) * 1.5 * 112) + 1 * 112
ELSE
((SELECT CAST(count(A.insID) AS DECIMAL) FROM OINS A WHERE A.customer = T0.U_CardCode AND (A.ItemName NOT LIKE '%%ENVIRO%%' AND A.ItemName NOT LIKE '%%Heat Seal%%' AND A.ItemName NOT LIKE '%%Printer%%' AND A.ItemName NOT LIKE '%%Commercial%%')) * 1.5 * 140) + 1 * 140
END AS 'Theoretical Charge'

FROM [dbo].[@SWA_CT_SUBSCR]  T0 
INNER JOIN OCRD T1 ON T1.CardCode = T0.U_CardCode
INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode

Which gives the following type of output

As you can see, both columns have different equipment ID's, what i would like to do is compare both strings and output the difference between them in a following column.

Here is how the code looks with just both columns I am trying to compare;

SELECT 

(SELECT TOP 1 A.[U_ACEqCodes] 
FROM [dbo].[@SWA_CT_SUBSCR_PLTPL]  A 
INNER JOIN [dbo].[@SWA_CT_SUBSCR]  B ON B.Code = A.U_SubscriptionCode 
WHERE B.[U_CardCode] = T0.U_CardCode 
AND CONVERT(NVARCHAR(MAX),A.[U_ACEqCodes]) <> '') AS 'Equipment in Subscription',
 
stuff((select '; '  + CAST(r.insID AS VARCHAR(10))
from OINS r  
where r.customer = T0.U_CardCode AND 
(r.ItemName NOT LIKE '%%ENVIRO%%' AND 
r.ItemName NOT LIKE '%%Heat Seal%%' AND 
r.ItemName NOT LIKE '%%Printer%%' AND 
r.ItemName NOT LIKE '%%Commercial%%')
FOR XML PATH('')), 1, 2, '') AS [Equipment from Cards]

FROM [dbo].[@SWA_CT_SUBSCR]  T0 
INNER JOIN OCRD T1 ON T1.CardCode = T0.U_CardCode
INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode

An example of what I am trying to achieve is;

String 1 = 7849;7985;8044;8253;8254;16733 String 2 = 7849; 16733; 8044; 7985

Outcome = 8253;8254

Any help is appreciated. My code isn't really the most optimal either, if there is a more optimal way of writing it I would be happy to learn.

Answer 1

First i Would Create a table Valued Function, which will split a string (or result of subquery) into individual values based on the supplied delimiter:

USE [maintanence]
GO
/****** Object:  UserDefinedFunction [dbo].[StringListToTable]    
Script 
Date: 05/07/2021 07:09:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[StringListToTable] (@list  nvarchar(MAX),
                                @delim nvarchar(10))
   RETURNS @tbl TABLE (listpos int NOT NULL IDENTITY(1,1),
                       n       int NULL) AS
BEGIN
   DECLARE @pos       int = 1,
           @nextpos   int = 1,
           @valuelen  int,
           @delimlen  int = datalength(@delim) / 2

   WHILE @nextpos > 0
   BEGIN
          SELECT @nextpos = charindex(@delim COLLATE Czech_BIN2, @list, @pos)
          SELECT @valuelen = CASE WHEN @nextpos > 0 THEN @n

extpos
                              ELSE len(@list) + 1
                         END - @pos
      INSERT @tbl (n)
         VALUES (convert(int, nullif(substring(@list, @pos, @valuelen), '')))
      SELECT @pos = @nextpos + @delimlen
   END
   RETURN
END

(thanks to this excellent article: https://www.sommarskog.se/arrays-in-sql.html )

Then I would use the function as follows (For clarity I have omitted your where clauses and only selected one column)

select 

  STUFF((SELECT ';' + CAST(x.equip AS VARCHAR(MAX))
     FROM (select n [equip] from StringListToTable(
                (select [Equip] FROM [Maintenance].[dbo].[testing]  ),';') 
                    where n not in (select cards from [Maintenance].[dbo].[Testing2] ) ) x
     FOR XML PATH('')),1,1,'') [Difference]

test tables:

Result: