To my knowledge, the only hybrid HDD in production for consumer is the Seagate Momentus XT.
I've actually tried the Seagate Momentus XT 320GB vs a Kingston SSDNow (not plus) Gen 2 128GB in both my laptop (as the only drive) and my desktop (as an OS drive).
When the Momentus XT's flash was boosting the performance (generally at boot and some program startups), I did not notice much difference from the SSD. Note that my SSD is a relatively low-end drive that I can actually cause stutter on if I try hard enough. When the Momentus XT flash is not helping (e.g. trying to run something you just installed) however, it is as slow as any other laptop hard drive.
The benefits for me for the hybrid HDD versus a regular HDD is the boost provided on frequently-used applications (it works better than SuperFetch because it has less activity during boot). As previous posters said, it cannot compare to an SSD (even a low-end one).
Note that the Momentus XT generally has higher power consumption than a lone SSD or HDD so you may want to watch out if you have an undercooled laptop.
If you're not storing a ton of stuff on a desktop, RAID 0 for Seagate Momentus XT looks rather promising:
Seagate Momentus XT 500GB ST95005620AS Review - Overclockers Club