CODEWITHSUNDEEP
mysql
sneha
sneha

Reputation: 17

SQL: GROUP BY Clause for Comma Separated Values

Can anyone help me how to check duplicate values from multiple comma separated value. I have a customer table and in that one can insert multiple comma separated contact number and I want to check duplicate values from last five digits.For reference check screenshot attached and the required output is contact_no. count 97359506775 -- 2 390558073039-- 1 904462511251-- 1

enter image description here

Upvotes: 0

Views: 185

Answers (2)

The Shooter
The Shooter

Reputation: 733

I don't remember much but I will try to put the idea (it's something which I had used a long time ago):

  1. Create a table value function which will take the id and phone number as input and then generate a table with id and phone numbers and return it.
  2. Use this function in query passing id and phone number. The query is such that for each id you get as many rows as the phone numbers. CROSS APPLY/OUTER APPLY needs to be used.
  3. Then you can check for the duplicates.

The function would be something like this:

CREATE FUNCTION udf_PhoneNumbers
(
    @Id INT
    ,@Phone VARCHAR(300)
) RETURNS @PhonesTable TABLE(Id INT, Phone VARCHAR(50))
BEGIN
    DECLARE @CommaIndex INT
    DECLARE @CurrentPosition INT
    DECLARE @StringLength INT
    DECLARE @PhoneNumber VARCHAR(50)

    SELECT @StringLength = LEN(@Phone)
    SELECT @CommaIndex = -1
    SELECT @CurrentPosition = 1
    --index is 1 based
    WHILE @CommaIndex < @StringLength AND @CommaIndex <> 0
    BEGIN
        SELECT @CommaIndex = CHARINDEX(',', @Phone, @CurrentPosition)

        IF @CommaIndex <> 0
            SELECT @PhoneNumber = SUBSTRING(@Phone, @CurrentPosition, @CommaIndex - @CurrentPosition)
        ELSE
            SELECT @PhoneNumber = SUBSTRING(@Phone, @CurrentPosition, @StringLength - @CurrentPosition + 1)

        SELECT @CurrentPosition = @CommaIndex + 1

        INSERT INTO @UsersTable VALUES(@Id, @PhoneNumber)
    END

    RETURN
END

Then run CROSS APPLY query:

SELECT 
    U.* 
    ,UD.*
FROM yourtable U CROSS APPLY udf_PhoneNumbers(Userid, Phone) UD

This will give you the table on which you can run query to find duplicate.

Upvotes: 0

everling
everling

Reputation: 67

I would advise you to redesign your database schema, if possible. Your current database violates First Normal Form since your attribute values are not indivisible.

Create a table where id together with a single phone number constitutes a key, this constraint enforces that no duplicates occur.

Upvotes: 1

Related Questions