There is no central realtor database with "condemned" homes (referring to homes that are not habitable). Your realtor can keep an eye out for distressed/fixer properties for you. You may want to check with the City of SF directly, too, to see if they maintain a separate database of "condemned" homes and work from there. Your realtor can also keep an eye out for foreclosed homes as well. And yes, financing on condemned homes may be challenging at best but can you try FHA (your realtor can tell you more about that as well).
Best of luck-
Zephyr Real Estate
Others have said it- work with a knowledgeable realtor, someone who will keep track of the market, scouts out properties for you, makes sure the value is there both before and after rehab, can give you cost effective suggestions as to how to improve the value of the property both short and long term.
415 860 0765
A "condemned" house is not HABITABLE; it probably burned and the City will not allow anyone to live there, it either needs to be re-built, or they will require it to be raised and start over with the foundation.
What I think you want is a house that has gone through Foreclosure and is being sold for a really low figure by the Bank: If this is what you want, the best way to do it is to have a Realtor looking for you; a Buyer's Agent cost you nothing, the Bank will pay his fee, but he will be representing YOU.
Good luck and may God bless