Q:

Return Distinct Rows That Contain The Same Value/Character In SQL

I have a bit of a tricky situation. I have a column that contains a pipe delimited set of numbers in numerous rows in a table. For example:
Courses
-------------------
1|2
1|2|3
1|2|8
10
11
11|12

What I want to achieve is to return rows where the number only appears once in my output.
Ideally, I want to try and carry this out using SQL rather than having to carry out checks at a web application level. Carrying out a DISTINCT does not achieve what I want.
The desired output would be:
Courses
-------------------
1
2
3
8
10
11
12

I would appreciated if anyone can guide me in the right direction.
Thanks.

A:

Please try:
declare @tbl as table(Courses nvarchar(max))
insert into @tbl values
('1|2'),
('1|2|3'),
('1|2|8'),
('10'),
('11'),
('11|12')

select * from @tbl

SELECT 
     DISTINCT CAST(Split.a.value('.', 'VARCHAR(100)') AS INT) AS CVS  
FROM  
(
    SELECT CAST ('<M>' + REPLACE(Courses, '|', '</M><M>') + '</M>' AS XML) AS CVS  
    FROM  @tbl 
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
ORDER BY 1

