Geek Answers Handbook

Removing duplicate rows in SQL Server

I want to remove duplicate rows in the specified column of the table.

Here are some examples:

Input     | Expected Output
---------------------------
XYXY      | XY
AA        | A
XYZXYZ    | XYZ
ABCABCABC | ABC

How can i do this?

+4
source share
2 answers

This request will be useful to you.

SELECT dbo.RemoveDuplicate (ColumnName, VariableLength) FROM TableName.

Example: SELECT dbo.RemoveDuplicate (StudentName, 20) FROM Students.

Function to remove duplicate line:

CREATE FUNCTION RemoveDuplicate (@sInputString AS VARCHAR(10), @nLength AS INT)
RETURNS VARCHAR(Max) AS  
BEGIN
    DECLARE @count INT
    DECLARE @new_string VARCHAR(Max)
    SET @count=1
    WHILE ( @count <=  @nLength )
      BEGIN
          IF ( @new_string IS NULL )
            BEGIN
                SET @new_string=''
            END
          SET @new_string=@new_string + Substring(@sInputString, 1, 1)
          SET @sInputString=REPLACE(@sInputString, Substring(@sInputString, 1, 1), '')
          SET @count=@count + 1
      END
    RETURN @new_string 
END
+5
source

Three logics were used to get the result.

The first finds different letters in each line withCTE

row_number() CTE, .

- concatenate the rows using group by row_number(), .

CREATE TABLE #input
  (name VARCHAR(50))

INSERT INTO #input
VALUES      ('XYXY'),
            ('AA'),
            ('XYZXYZ'),
            ('ABCABCABC');

WITH cte
     AS (SELECT Row_number()OVER (ORDER BY name)    rn,
                Substring(name, 1, 1) AS sub,
                1                     AS IDX,
                name
         FROM   #input
         WHERE  Len(name) > 0
         UNION ALL
         SELECT rn,Substring(name, IDX + 1, 1) AS sub,
                IDX + 1                     AS IDX,
                name
         FROM   cte
         WHERE  IDX < Len(name))
SELECT name INPUT, (SELECT DISTINCT CONVERT(VARCHAR(100), sub)
                 FROM   cte b
                 WHERE  b.rn = a.rn
                 FOR XML PATH('')) EXPECTED_OUTPUT
FROM   cte a
GROUP  BY rn ,name

OUTPUT

INPUT       EXPECTED_OUTPUT
---------   ---------------
AA          A
ABCABCABC   ABC
XYXY        XY
XYZXYZ      XYZ
+1

More articles: