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

i have bit of tricky situation. have column contains pipe delimited set of numbers in numerous rows in table. example:

courses ------------------- 1|2 1|2|3 1|2|8 10 11 11|12 

what want achieve return rows number appears once in output.

ideally, want try , carry out using sql rather having carry out checks @ web application level. carrying out distinct not achieve want.

the desired output be:

courses ------------------- 1 2 3 8 10 11 12 

i appreciated if can guide me in right direction.

thanks.

please try:

declare @tbl table(courses nvarchar(max)) insert @tbl values ('1|2'), ('1|2|3'), ('1|2|8'), ('10'), ('11'), ('11|12')  select * @tbl  select       distinct cast(split.a.value('.', 'varchar(100)') int) cvs     (     select cast ('<m>' + replace(courses, '|', '</m><m>') + '</m>' xml) cvs        @tbl  ) cross apply cvs.nodes ('/m') split(a) order 1