Remove dup contents in a row 2

[gatetec]

I had dup records, and after some manipulations, I was able to remove the dup records. However, the dup contents in each record remain. How do you remove the dup contents in a row.

clientno reason
415 Lack of Support, Lack of Support
416 Lack of Support, Lack of Support
418 Lack of Support, Lack of Support
420 Lack of Support, Lack of Support
421 Emotional Problems, Emotional Problems, Emotional Problems, Lack of Support
422 Emotional Problems, Emotional Problems, Emotional Problems, Lack of Support

Please advise.

 

[JohnDTampaBay]

SELECT Reason, Min(ClientNo) AS ClientNo
FROM yourtable GROUP BY Reason

How does this work for you?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[gatetec]

Abuse/Neglect, Abuse/Neglect 578
Accident, Abuse/Neglect, Accident, Accident 783
Accident, Accident 22
Accident, Accident, Accident, Cognitive Problems 679

The dup contents still how up.

Pls advise.

 

[gmmastros]

This is really ugly, no matter how you look at it. Is this a one-time thing where you need to clean up some data, or do you need to run this regularly?




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gatetec]

At this moment, I need to run a batch for the initial clean up. I might need to run a few more times.
Is there any way to clean this up?

thx much

 

[gmmastros]

Is there any way to clean this up?

Yes. But you should expect this to be slow. That's why I asked if this was something you needed to do regularly. It's ok for something to be slow if you're only running it once.

I would approach this by first writing a function that removes duplicates from a comma separated list of data, like this...

Create Function RemoveDups(@Data VarChar(8000))
Returns VarChar(8000)
As
Begin

  Declare @Temp Table(Data VarChar(8000))

  While CharIndex(',', @Data) > 0
    Begin

      Insert Into @Temp(Data)
      Select LTrim(RTrim(Left(@Data, CharIndex(',', @Data)-1)))

      Select @Data = LTrim(RTrim(Right(@Data, Len(@Data) - CharIndex(',', @Data))))

    End

  Insert Into @Temp Values(@Data)

  Declare @Output VarChar(8000)
  Set @Output = ''

  Select @Output = @Output + Data + ', '
  From   (Select Distinct Data From @Temp) As A

  If Right(@Output, 2) = ','
    Set @Output = Left(@Output, Len(@Output)-1)

  Return @Output
End

Then, you should test this to make sure it is doing what you expect (before updating your data). Like this..

Select ClientNo, Reason, dbo.RemoveDups(Reason) As NoDups
From   YourTable

If this looks right, then you can update/fix your data, like this...

Update YourTable
Set    Reason = dbo.RemoveDups(Reason)

I strongly encourage you to make a backup before running this, just in case something wacky happens.

Good luck.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

I would also striongly consider redesigning this. You should never store data this way. This duplication is one of the reasons why. The difficulty of querying is the other. Rule number 1 of database design - never put more than one piece of information in a field. This should have a realted table instead which can then have a unique constraint and duplicates wil never be allowed to added. You can fix this now as George showed you, but in six months or less, you will be back in the same place fixing it again.

"NOTHING is more important in a database than integrity." ESquared

 

[gatetec]

thx so much. The function works fine!!!
It is GREAT!