I know that passwords are not stored in plain text (or at least, that they shouldn't...), but can you still compare their hashes to know if 2 users have the same password?
If you're using salted hashes, then no, you can't, as the salt will be different for each user.
And if you're not using salted hashes, start using salted hashes.
But you know the salt used for each user (you have to, otherwise how do you check if the user typed in the right password?), so you can still use that to check if the password is the same as another user? Essentially, mimick the process of trying to login to every user account with the new user password and see if that matches.
OK, I get that it might be prohibitively expensive (checking all accounts whenever a new one is created...), especially if you have a lot of accounts, which is when this kind of process is useful anyway. But I guess there might be smart ways to optimise that (start by checking the passwords that have already been identified in the past as potential spammers etc.).
I don't know, totally bad idea, TDWTF front-page material or just impractical and not that useful anyway?