I know almost nothing about Upshaw, and the little I know makes me think that the C's should/will steer clear of him.
That said, there have been many guys with personality, MH, or substance issues who had productive careers. A few examples: Vin Baker, Chris Mullen, Ron Artest, Dennis Rodman and Delonte West (well, a reasonably productive career for DW). Baker was eventually done in by his problems, but not before many 20/10 seasons. Mullen got completely past his issues. Artest and Rodman had great careers. Of course, lots of examples of guys who never made it.
I do believe that the support system in place makes the difference for a lot of players. Nowadays, teams should be able to provide better supports. We'll see what happens with Royce White.