What is wanted first and foremost is a hashing algorithm that is slow. Crackers crack password hashes by running password dictionaries and other combinations of characters through the hash algorithm to see whether the computed hash matches the one stolen from a database. The slower you can make that process, the harder it will be to "reverse" your hashes if they're stolen.
Next, every password needs a different random "salt" concatenated with it before computing the hash. The cracker who gets the password hashes will be able to get the salts, too, but the presence of the salt foils precomputation attacks. Every hash must be attacked separately.
Finally, you should consider using a secret key not stored in a database as a component of the hash. This is sometimes called a "pepper" and the result is a keyed hash. The attacker who gets hashes through something like SQL injection will not be able to crack them because that secret key is needed. (Of course, the attacker who compromises the OS can get everything, but that's significantly harder than just compromising a database.)
The question (and answer) noted by Xander have thorough coverage: How to securely hash passwords? There's a good essay here: https://crackstation.net/hashing-security.htm and some more advice here: http://bitmonger.blogspot.com/2012/07/six-simple-rules-for-secure-storage-of.html