Filter Duplicates based on string

• PSB

  SSChampion

  Points: 13195

  More actions

  February 19, 2014 at 11:32 am

  #285809

  CREATE TABLE #Names

  ( ID INT IDENTITY(1,1),

  NAME VARCHAR(100)

  )

  INSERT INTO #Names VALUES ('S-SQLXX')

  INSERT INTO #Names VALUES ('S-SQLXX.NA.SN.ORG')

  INSERT INTO #Names VALUES ('S-SQLYY')

  INSERT INTO #Names VALUES ('S-SQLYY.NA.SN.ORG')

  INSERT INTO #Names VALUES ('S-SQLCL-HR')

  INSERT INTO #Names VALUES ('S-SQLCL-MIS')

  SELECT * FROM #Names

  --I want to filter out S-SQLXX.NA.SN.ORG because S-SQLXX.NA.SN.ORG is a duplicate of S-SQLXX eliminating .NA.SN.ORG from it.

  --I want to filter out S-SQLYY.NA.SN.ORG because S-SQLYY.NA.SN.ORG is a duplicate of S-SQLYY eliminating .NA.SN.ORG from it.

  --However I want to keep S-SQLCL-HR and S-SQLCL-MIS in my list of names as they do not have .NA.SN.ORG as a part of their name

  --I want ONLY these returned IN the SELECT

  SELECT * FROM #Names WHERE ID IN (1,3,5,6)

  DROP TABLE #Names

  Thanks,

  PSB

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 19, 2014 at 11:50 am

  #1690404

  Maybe something like this if you just want to eliminate duplicates while displaying the information:

  WITH CTE AS(

  SELECT *,

  ROW_NUMBER() OVER(PARTITION BY LEFT( Name, CHARINDEX( '.', Name + '.') - 1) ORDER BY Name) rn

  FROM #Names

  )

  SELECT ID, NAME

  FROM CTE

  WHERE rn = 1

  ORDER BY ID

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2