Note my use of the word "unmanaged". It's possible to house people with active substance abuse problems, but it's considerably more challenging, particularly heavy drinkers and crack users. There is a significant impact in terms of care because you're dealing with people who have a limited capacity to look after themselves, but the primary impediment is aggression and violence. It only takes one difficult resident to turn the whole facility into bedlam, at which point you're faced with few options other than evicting them for the sake of the other residents.
Wet houses do exist, but they're a lot more expensive to run and they face far more local opposition. They're a fundamentally good idea, but housing alcoholics and other difficult-to-house groups is a lot more complex than just "open more shelters". You need a highly skilled multidisciplinary team to make it work, and you're still going to have some amount of unavoidable rough sleeping.
If you still don't see where I'm coming from, imagine a crack addict with diagnosed antisocial personality disorder. Imagine someone who has been arrested multiple times for assaulting medical staff and police officers. Imagine someone whose life revolves around multi-day crack binges, which usually end in an episode of drug-induced psychosis and either an ER visit or an arrest. These people are not exceptional in the population of chronic rough sleepers, but they're exceptionally difficult to house.
Sometimes the best answer is to just give them their own space instead of a shared facility.
I've had plenty of contact with the homeless, have housed them in my own home for a night or three. They all have at least some kind of substance abuse problem (every one smoked, for instance). But if you push them out on the street, they'll end up eventually in the ER or in contact with police. This is way more expensive.
Housing is a crucial part of the response to homelessness, but it's only a part. Some people can walk straight into a home and get back on their feet, but some people will need intensive support and others just aren't ready to be housed.
Good luck.
When you make housing for the homeless dependent on being sober and clean, just what, exactly, do you expect as an outcome if not that?
THIS moralizing "Oh, but you've got to DESERVE the help!" is partly why homelessness (and poverty) are still major problems in the US.
(Oh, and the truly pathetic NIMBYism in San Fran doesn't help anything... It makes solving the housing problem in general intractible. Seriously, shut up about the "character of the city" when you're really just using that as an excuse to increase your home value and when that "character" leads to a whole bunch of homelessness and rent that is impossible for people seeking jobs to afford.)