CODEWITHSUNDEEP
sqlsql-server
Surinder Bhomra
Surinder Bhomra

Reputation: 2199

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.

Upvotes: 1

Views: 262

Answers (5)

Ravi Singh
Ravi Singh

Reputation: 2080

Try this :

create table course (courses varchar(100))

insert into course values('1|2')
insert into course values('1|2|3')
insert into course values('1|2|8')
insert into course values('10')
insert into course values('11')
insert into course values('11|12')

Declare @col varchar(200)

SELECT
@col=(
        SELECT DISTINCT  c.courses + '|'
        FROM course c

        FOR XML PATH('')
      );




select * from course

;with demo as(


select cast(substring(@col,1,charindex('|',@col,1)-1) AS INT) cou,charindex('|',@col,1) pos

  union all 
  select cast(substring(@col,pos+1,charindex('|',@col,pos+1)-pos-1)AS INT) cou,charindex('|',@col,pos+1) pos
  from demo where pos<LEN(@col))
select distinct cou from demo

Upvotes: 1

Devart
Devart

Reputation: 122042

Try this one -

SET NOCOUNT ON;

DECLARE @temp TABLE
(
      string VARCHAR(500)
)

DECLARE @Separator CHAR(1)
SELECT @Separator = '|'

INSERT INTO @temp (string)
VALUES 
    ('1|2'),
    ('1|2|3'),
    ('1|2|8'),
    ('10'),
    ('11'),
    ('11|12')

-- 1. XML

SELECT p.value('(./s)[1]', 'VARCHAR(500)')
FROM (
    SELECT field = CAST('<r><s>' + REPLACE(t.string, @Separator, '</s></r><r><s>') + '</s></r>' AS XML) 
    FROM @temp t
) d
CROSS APPLY field.nodes('/r') t(p)

-- 2. CTE

;WITH a AS
(
    SELECT 
          start_pos = 1
        , end_pos = CHARINDEX(@Separator, t.string)
        , t.string
    FROM @temp t

    UNION ALL

    SELECT 
          end_pos + 1
        , CHARINDEX(@Separator, string, end_pos + 1)
        , string
    FROM a
    WHERE end_pos > 0
)
SELECT d.name 
FROM (
    SELECT 
          name = SUBSTRING(
              string
            , start_pos
            , ABS(end_pos - start_pos)
        ) 
    FROM a
) d
WHERE d.name != ''

Upvotes: 1

user359040
user359040

Reputation:

One way would be to use a recursive CTE:

with cte as
(select cast(case charindex('|',courses) when 0 then courses
                  else left(courses,charindex('|',courses)-1) end as int) course, 
        case charindex('|',courses) when 0 then ''
             else right(courses,len(courses)-charindex('|',courses)) end courses 
 from courses
 union all
 select cast(case charindex('|',courses) when 0 then courses
                  else left(courses,charindex('|',courses)-1) end as int) course, 
        case charindex('|',courses) when 0 then ''
             else right(courses,len(courses)-charindex('|',courses)) end courses 
 from cte
 where len(courses)>0)
 select distinct course from cte

SQLFiddle here.

Upvotes: 0

TechDo
TechDo

Reputation: 18659

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

Upvotes: 3

Davide
Davide

Reputation: 508

Could not manage without recursion :( Something like this could do the trich?

WITH splitNum(num, r)
AS
(
  SELECT
   SUBSTRING(<field>,1, CHARINDEX('|', <field>)-1) num,
   SUBSTRING(<field>,CHARINDEX('|', <field>)+1, len(<field>)) r
  FROM <yourtable> as a
  UNION ALL
  SELECT
    SUBSTRING(r,1, CHARINDEX('|', r)-1) num,
    SUBSTRING(r,CHARINDEX('|', r)+1, len(r)) r
  FROM <yourtable> b
  WHERE CHARINDEX('|', r) > 0
  inner join splitNum as c on <whatevertheprimarykeyis>
 )
 SELECT distinct num FROM splitNum

Didn't make it run, but it should do the trick, just replace the and with the correct info

Upvotes: 0

Related Questions