how to remove duplicates from sql server based on string

Question

removing duplicates from SQL table

example : i/p :

EID EName ....  ERole    
1    Nani       SQL    
2    Nani       SQL Developer     
3    Suresh     ASP .Net Developer     
4    Suresh     ASP .Net    
5    Ravi       Sales Force    
6    Ravi       Sales Force developer 

i have data set like above ,

Output :

EID EName ....  ERole     
2    Nani       SQL Developer     
3    Suresh     ASP .Net Developer     
6    Ravi       Sales Force developer 

Note above one is example :

in ERole column , if first 10 characters are matched it should be a duplicate .

Answer 1

First im going to set up your example so you can test it.

CREATE TABLE #example
(
    EID INT PRIMARY KEY IDENTITY,
    EName VARCHAR(100),
    ERole VARCHAR(MAX)
)

INSERT INTO 
    #example
VALUES
    ('Nani','SQL'),
    ('Nani','SQL Developer'),
    ('Suresh','ASP .Net Developer'),
    ('Suresh','ASP .Net'),
    ('Ravi','Sales Force'),
    ('Ravi','Sales Force developer')

Now, if you want to delete duplicated record by your criteria(Same Ename, Same first 10 chars in ERole) u can use this:

WITH grouped_example as (
SELECT
        ROW_NUMBER() OVER(PARTITION BY Ename, SUBSTRING(ERole,0,10) ORDER BY ERole DESC) as preserve,
        EID
    FROM #example
)
DELETE FROM grouped_example where preserve <> 1

If u want to SELECT with no duplicates u can use this:

WITH grouped_example as (
SELECT
        ROW_NUMBER() OVER(PARTITION BY Ename, SUBSTRING(ERole,0,10) ORDER BY ERole DESC) as preserve,
        *
    FROM #example
)
SELECT EID,EName,ERole FROM grouped_example where preserve = 1

*Note: I use ORDER BY ERole DESC so that we stay with the role with more information (more characters) *Note 2: you can change the number of characters you need to match, changing the last value of SUBSTRING()