How have you explored this idea, in characters or in campaigns, in your own games? What ideas do you have for using it in adventure and character concepts?
A little bit. In my email campaign (role-playing intensive), the party leader is an atheist fighter. His best friend is a cleric (party member), in a relationship of opposites. The fighter believes in clerical magic -- I've never seen a D&D character who did believe in magic (!) -- but he thinks the gods are hooey.
The easiest way to explain it is that he's like Han Solo -- just because his pals manifest magical powers and he's impressed by their achievements doesn't mean he won't say, "Hokey religions and ancient weapons are no match for a good blaster at your side."
Most characters in my campaigns are devoted to a single deity.
But I have a wizard/sorcerer who is "broadminded". He reveres Fharlanghn, but he also respects his father's god (Rao), his mother's god (Pelor), the god of the party cleric (St. Cuthbert), and he has a working interest in Dalt (the god of gates, keys, etc.) given the subject matter of the later god.
We also had a character who was agnostic. He was raised in the faith of the Pelor (the largest religion in my campaign), but he never really had much opinion or interest in religion.
I enjoy all this diversity, as it seems realistic to me.
In real life, I've known deeply religious folks of a single faith, lots of agnostic/not that interested types, moderately religious folks, religious people whose faith makes them virulently conservative (they say), religious people whose faith makes them virulently leftist (they say), a guy who's essentially a pagan but interested in lots of religions, several atheists ranging from quiet about it to evangelistic about it, and a few folks who have an eclectic approach to religion.
The eclectic approach is common in Asia. My wife (from Singapore) calls herself a free-thinker. Her parents would say they are Buddhist. But it's nothing like "Buddhism" as Westerners typically think about it (as monolithic, organized religion with a particular set of beliefs and rules, like Christianity, yet more hip and open). Instead it's the traditional Chinese version -- a little bit of Buddhism, respect for Buddhist monks and temples, but also some fortunetelling (by monks), lots of traditional superstitions, a family altar at grandma's place to the founder of the family (ancestor worship), Chinese folks religion/tradition like burning joss paper and telling stories about the mother goddess, the monkey god, etc. Oh yeah, and they also go to Christian churches of various denominations with their friends, and her uncle is a devout Catholic.
That to me, is "how D&D world should be" -- lots of different faiths coexisting, sometimes in the same person, sometimes in different people in the same community. And in that milieu, fitting in an atheist or agnostic is not a big deal -- there's so much diversity of faith, another approach isn't surprising or threatening to most people.