Compare text column to find the difference if any

Question

Hi, I want to compare Scripting column (data type is text) of #A and #B. Is there a way to compare and find the differences? I want to get desired result or any possible output which is showing the differences. Thanks

Create table #A (Code int, Scripting text)

insert into #A values (102,'')

insert into #A values (103,'')

insert into #A values (104,'')

Update #A set Scripting =''Code is right

                                Linked on HR

                                       reference-562622'' where code='102'

Update #A set Scripting =''Severance paid equally

                                Linked on CT

                                       reference-377344'' where code='103'

Update #A set Scripting =''CR renewal

                                Linked on AW

                                       reference-388344'' where code='104'

Create table #B (Code int, Scripting text)

insert into #B values (102,'')

insert into #B values (103,'')

insert into #B values (104,'')

Update #B set Scripting =''Code is right

                                Linked on HR

                                       reference-562622'' where code='102'

Update #B set Scripting =''Severance paid equally

                                Linked on TT

                                       reference-377366'' where code='103'

Update #B set Scripting =''CR renewal

                                Linked on AW

                                       reference-388399'' where code='104'

Expected result:

Code Scripting Difference_A Difference_B

102 No Difference - -

103 Difference Linked on CT Linked on HR

                                                         reference-377344    reference-377366

104 Difference reference-388344 reference-388399

Answer 1

If it has to be done in SQL, it seems complicated. For example:

;
with Q as
(
	select a.Code, vl.a as vla, vl.b as vlb, vr.a as vra, vr.b as vrb
	from #A a 
	inner join #B b on b.Code=a.Code
	cross apply (values (concat(a.Scripting, ' '), concat(b.Scripting, ' '))) t(a, b)
	cross apply (values (charindex('Linked on ', t.a), charindex('Linked on ', t.b))) sl(a, b)
	cross apply (values (charindex(' ', t.a, sl.a + len('Linked on ') + 1), charindex(' ', t.b, sl.b + len('Linked on ') + 1))) el(a, b)
	cross apply (values (substring(t.a, sl.a, el.a - sl.a), substring(t.b, sl.b, el.b - sl.b))) vl(a, b)
	cross apply (values (charindex('reference-', t.a), charindex('reference-', t.b))) sr(a, b)
	cross apply (values (charindex(' ', t.a, sr.a + len('reference-') + 1), charindex(' ', t.b, sr.b + len('reference-') + 1))) er(a, b)
	cross apply (values (substring(t.a, sr.a, er.a - sr.a), substring(t.b, sr.b, er.b - sr.b))) vr(a, b)
)
select Code, 'No difference' as Scripting, '-' as Difference_A, '-' as Difference_B
from Q
where vla = q.vlb and vra = vrb
union all
select Code, 'Difference', vla, vlb
from Q
where vla <> vlb
union all
select Code, 'Difference', vra, vrb
from Q
where vra <> vrb
order by Code