Remove Duplicate Rows If n characters are match

Question

I have run time table which give me out-put like:

NO  COMPANY NAME        CONTACT NO
1   ABC DEF PVT         1234567890
2   ABC DEF PVT.        1234567890
3   XYZ ABC LTD         987654321
4   XYZ ABC LTD PVT.    987654321

Here, NO is Primary Key.

I want to show only 1st & 3rd row.

My condition is first 5 character are match than and only consider duplicate row and remove it. i.e. remove 2nd & 4th.

You need to search on GOOGLE, lots of solutions. Check this : http://stackoverflow.com/questions/3317433/delete-duplicate-records-in-sql-server — Paresh J, Nov 26 '14 at 07:49

Arion · Answer 1 · 2014-11-26T08:11:05.960

This is a suggestion on how to exclude the duplicates

Test data:

DECLARE @tbl TABLE(NO INT,[COMPANY NAME] VARCHAR(100),[CONTACT NO] VARCHAR(100))
INSERT INTO @tbl
VALUES
(1,'ABC DEF PVT','1234567890'),
(2,'ABC DEF PVT.','1234567890'),
(3,'XYZ ABC LTD','987654321'),
(4,'XYZ ABC LTD PVT.','987654321'),
(5,'ABC DEF PVT','9994567890')

Query:

;WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY SUBSTRING([COMPANY NAME],1,5),[CONTACT NO] 
                          ORDER BY NO) AS RowNbr,
        tbl.*
    FROM
        @tbl AS tbl
)
SELECT
    *
FROM
    CTE
WHERE
    CTE.RowNbr=1

To accomplish the same result you could also do it without a common table expression. Like this:

SELECT
    *
FROM
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY SUBSTRING([COMPANY NAME],1,5),[CONTACT NO] 
                          ORDER BY NO) AS RowNbr,
        tbl.*
    FROM
        @tbl AS tbl
) AS t
WHERE t.RowNbr=1

Result:

1   1   ABC DEF PVT 1234567890
1   3   XYZ ABC LTD 987654321
1   3   XYZ ABC LTD 987654321

Reference:

But, one more senario I have that, 5 'ABC DEF PVT','9994567890', than is this work? So, I want to show this row also. — Chirag Khatsuriya, Nov 26 '14 at 07:59
@VDohnal my output shold be 1,'ABC DEF PVT','1234567890' , 5 'ABC DEF PVT','9994567890', because companies are same but number are different. ( row number 1, 2 , 5 are for same company with different contact number ), due to mistake in data entry this count as different company for ABC. I have use group by with Contact Number and Having count > 1. — Chirag Khatsuriya, Nov 26 '14 at 08:06

Remove Duplicate Rows If n characters are match

1 Answers1