I used to like them. My favorite were nipple piercings (I got mine done in 94) because they looked good and indicated a woman who really, really liked sex. The same was largely true for genital piercings. Over the years, they are much less indicative of sexual enthusiasm and have come to be kind of an institutionalized rebellion (I'm a non-conformist, this is our uniform). As it became more common for people to have a piercing, people got more and more obvious piercings.
Currently, I find them kind of dull and the presence of a lot of obvious piercings is more often than not and indication that that person wants attention and not hot sex.
I find them as a turn on somewhat, but I could never date a girl with one. Especially not if it's in an obvious place like her lip. Kinda like a bisexual girl. It's a turn on when I find out a girl is bisexual, but I could never date a girl who was.
Personally, I like only ear piercings on women, and maybe possible belly button. The lip piercings make me wonder what it would be like to kiss her. I don't want to kiss metal. Nose piercings have never really looked right to me. Even the new little sparky ones you barely notice. I've known a few people with tongue rings and sometimes the results of the piercing is an annoying little clicking noise when the ring hits the teeth. And as for other places, yeah lets just not go there with metal.
But that's just my opinion. I know lots of people who like piercings and think they are sexy. It's very much a personal thing, just like tattoos. Just like the people difference between those who will get piercings and those who wouldn't, there are those who like piercings on others and those who don't.