IMO, Hungarian sweet paprika is darker, sweeter and more intense than the Spanish pimentón. They can usually not be substituted for each other. My simple principle is: Hungarian food - Hungarian paprika, Spanish food - Spanish paprika. The difference is not so great with the hot paprikas.
There are several qualities of Hungarian paprika (yes, they take this very seriously!). Különleges is the top quality, but hard to find outside Hungary. Csípősmentes csemege is also excellent, but the one you usually find abroad is édesnemes. It's acceptable. Erős is hot paprika.