Get Array Difference

Question

How do you return the difference?

CREATE TABLE Table1
(
    fname VARCHAR(25) NOT NULL,
    val VARCHAR(500) NOT NULL
)
INSERT INTO Table1 VALUES
('Bob','apple, orange, grape')

CREATE TABLE Table2
(
    fname VARCHAR(25) NOT NULL,
    val VARCHAR(500) NOT NULL
)
INSERT INTO Table2 VALUES
('Jane','pear, lemon, orange')

If returning the difference results from Table1 it should look like this result.

select 'Bob' as fname,'apple, grape' as val

If returning the difference results from Table2 it should look like this result.

select 'Jane' as fname,'pear, lemon' as val

Answer 1

Hi @PeterP1550

You can try this.

;with T1 as(
  select value from table1 cross apply string_split(val,',')
  except
  select value from table2 cross apply string_split(val,',')
),T2 as(
  select STRING_AGG(value,',') as val from T1)
select fname,T2.val from table1,T2;

Output:

---

;with T1 as(
  select value from table2 cross apply string_split(val,',')
  except
  select value from table1 cross apply string_split(val,',')
),T2 as(
  select STRING_AGG(value,',') as val from T1)
select fname,T2.val from table2,T2;

Output:

Best regards,

Percy Tang

Answer 2

The result set format don't make sense, because it could return 0-n records.

To get the data differents between 2 tables, see Set Operators - EXCEPT and INTERSECT (Transact-SQL)