SQL Server : searching duplicate record

Question

I'm trying to check for duplicate bame in SQL Server.

Table Company:

   CompanyName            | Business
   -----------------------+----------
   Example Company INC    | Telecom 
   The Example Company    | Telecom

I want to get the both of the company name as duplicate. using the parameters "The Example Company INC".

When I use

SELECT CompanyName 
FROM COMPANY 
WHERE CompanyName LIKE '%The Example Company INC%'

it show no results. The query must return 2 records - is this possible?

You can check like this `SELECT CompanyName FROM COMPAY WHERE CompanyName LIKE '%Example Company%' ` — Aruna, Jan 04 '17 at 04:48
please check your table name and you should remove INC from Where clause. — Ravi Matani, Jan 04 '17 at 04:50
@RaviMatani The parameter is as is.. and should return 2 records.. — Alvie, Jan 04 '17 at 04:55

score 1 · Answer 1 · edited May 23 '17 at 11:53

1

If this is the only case where one company is 'Example Company INC' and another company is 'The Example Company' then the following query can work

SELECT CompanyName FROM COMPANY WHERE CompanyName LIKE '%Example Company%'

But i think there can be many companies with different names. So in this case, I think for you the best is to find the similarity between two strings. And on the base of similarity factor you can decide whether they are same or not.

If your case is second one then this post can help you

edited May 23 '17 at 11:53

Community

1
1

answered Jan 04 '17 at 04:59

Abbas

763
1
12
26

The second one is correct. I need to find if the string are somewhat similar. On the example i have given the words "The" and "INC" are making the string unique. – Alvie Jan 04 '17 at 05:11

Shakeer Mirza · Accepted Answer · 2017-01-04T05:42:39.927

Split the string and Go with Dynamic coding

DECLARE @QRY VARCHAR(MAX), @DIVISIONS  VARCHAR(MAX)='The Example Company INC';

    DECLARE @START INT=1, @END INT=LEN(@DIVISIONS)
    , @LIKE VARCHAR(MAX)='CompanyName LIKE ''%';

    WHILE (@START<=@END)
    BEGIN

    SELECT @LIKE =@LIKE+  CASE ASCII(SUBSTRING(@DIVISIONS, @START, 1))
        WHEN 32
            THEN '%'' OR CompanyName LIKE ''%'
        ELSE SUBSTRING(@DIVISIONS, @START, 1)
        END

    SET @START+=1

    END
    SET @LIKE = @LIKE+'%'''

    SELECT @QRY = ' SELECT CompanyName FROM COMPANY 
    WHERE ' + @LIKE

    PRINT @QRY


    EXEC(@QRY)

Another simple way of Dynamic Query:

DECLARE @QRY VARCHAR(MAX), @DIVISIONS  VARCHAR(MAX)='The Example Company INC'
,@LIKE VARCHAR(MAX)='CompanyName LIKE ''%';

DECLARE @END INT=LEN(@DIVISIONS)
select @LIKE = @LIKE+   CASE ASCII(SUBSTRING(@DIVISIONS, number, 1))
        WHEN 32
        THEN '%'' OR CompanyName LIKE ''%'
ELSE SUBSTRING(@DIVISIONS, number, 1)
END
FROM master.dbo.spt_values 
where type='p' and number between 1 and @END

SET @LIKE = @LIKE+'%'''
SELECT @QRY = ' SELECT CompanyName FROM COMPANY 
WHERE ' + @LIKE

PRINT @QRY


EXEC(@QRY)

Thanks, this works. I was also trying to split the strings. but I'm not that good at tsql. Thanks again. — Alvie, Jan 04 '17 at 06:19
Shakeer Mirza can you explain this line of code me.. "CASE ASCII(SUBSTRING(@DIVISIONS, number, 1)) WHEN 32" Thanks. — Alvie, Jan 04 '17 at 06:34
Its Just checking the character is Space or not. 32 is the ascii value of Space character @Alvie — Shakeer Mirza, Jan 04 '17 at 06:37

SQL Server : searching duplicate record

2 Answers2