Duplicate validation in Sql

Question

I have a table that contains two columns where I check for duplicates. I wrote code for duplicates and

        DbCommand cmd = db.GetSqlStringCommand("SELECT MyTable.* FROM MyTable. INNER JOIN(SELECT MainUrl, COUNT(*) as counter FROM MyTable GROUP BY MainUrl HAVING COUNT(*) >1) t ON t.MainUrl=MyTable.MainUrl");
        int count = db.ExecuteNonQuery(cmd);
        DataSet ds = db.ExecuteDataSet(cmd);
        return ds;

While in my columns I have Amazon.com, http: //Amazon.com,https: //Amazon.com.I even want these two duplicates. I want to return all the columns in my table with these duplicates. But from my code I cannot achieve this, can anyone help me with this?

Answer 1

Using CHARINDEXand SUBSTRING:

He considers 'amazon.com', http://amazon.com'and https://amazon.com'as duplicates.

WITH MyTable(OriginalURL) AS(
    SELECT 'http://Amazon.com' UNION ALL
    SELECT 'https://Amazon.com' UNION ALL
    SELECT 'Amazon.com' UNION ALL
    SELECT 'http://Stackoverflow.com' UNION ALL
    SELECT 'Stackoverflow.com' UNION ALL
    SELECT 'http://google.com'
)
SELECT 
    CASE 
        WHEN CHARINDEX('http://', OriginalURL, 1) > 0 THEN SUBSTRING(OriginalURL, 8, LEN(OriginalURL) - 7) 
        WHEN CHARINDEX('https://', OriginalURL, 1) > 0 THEN SUBSTRING(OriginalURL, 9, LEN(OriginalURL) - 8) 
        ELSE OriginalURL
    END AS OriginalURL
    , COUNT(*) AS DupeCount
FROM MyTable
GROUP BY
    CASE 
        WHEN CHARINDEX('http://', OriginalURL, 1) > 0 THEN SUBSTRING(OriginalURL, 8, LEN(OriginalURL) - 7) 
        WHEN CHARINDEX('https://', OriginalURL, 1) > 0 THEN SUBSTRING(OriginalURL, 9, LEN(OriginalURL) - 8) 
        ELSE OriginalURL
    END
HAVING COUNT(*) > 1

Answer 2

Given one field. We can use this:

SELECT field, Count(*) FROM table
GROUP BY field
HAVING Count(*) > 1
ORDER BY Count /*optional DESC*/