Thanks for making this thread! I'm gonna enjoy this.
In approximate descending order of greatness. Spoilers throughout.
CLOUD STRIFE
Final Fantasy VII
Not only the best video game character I've ever encountered, but one of the best fictional characters from any medium. Throughout the game he goes from sensitive, idealistic kid to callous mercenary to, finally, an emotionally mature young man. He bears the weight of the world on his shoulders, is all too aware of his own flaws, and eventually ends up taking on the world's injustices with nothing but earnest good intentions, a handful of good friends and a FUCKING MASSIVE SWORD. Anyone who doesn't think he's a good character needs to re-evaluate their criteria, frankly.
GUYBRUSH THREEPWOOD
Monkey Island series (but mainly the first two)
The definitive wannabe pirate. Totally incompetent, socially awkward, cringe-inducingly stupid - and yet lovable. I wish there were more funny, playful, well-written characters like this in games nowadays instead of all these brooding, muscle-bound ugs. Oh wait, I know...
GLADOS
Portal series
The kind of villain who in almost all games would be instantly forgettable, but purely by virtue of excellent writing, she's one of the greatest game characters ever. Wheatley gets an honorary mention here too!
MANUEL CALAVERA
Grim Fandango
Yep, another vote for Manny (and the second LucasArts adventure game character in my list!) Possibly the most downright sympathetic character I've ever encountered in a game. I absolutely, unreservedly wanted everything to turn out ok for Manny, in the way I want things to turn out ok for my closest friend. I can think of some really great games that have not achieved that.
SEIFER ALMASSY
Final Fantasy VIII
This guy's character arc - from simple arrogant douchebag to increasingly unhinged and brainwashed lapdog to the evil sorceress - is frankly chilling. He reminds me of no one so much as Edmund from the Narnia stories, except that for Seifer there's no chance of redemption. I love the way you fight him several times throughout the game, and each time he's a little more deranged. His power as a character rests on carefully thought out motivations and desires of his own - he exists and acts in his own right, rather than simply in relation to the hero.
MORRIGAN
Dragon Age: Origins
She just plain irritated me to begin with, but once my character ended up boning her she started to grow on me. Funny, that. But she's a complex and fairly interesting character, and, again, I really got the impression she was a real person with real motivation and thoughts and desires of her own, rather than just a random stranger tagging along as so many RPG party members tend to be.
YOSHIMITSU
Tekken and Soul Calibur series
Ok, not much of a personality to speak of; not even a character in the traditional sense of the word, but taking him on his own merits, he's frankly awesome. He's a frequently revamped marvel of character design, and his whacky antics and "crusader of justice" persona really appeal to me, and make me laugh.
ALYX VANCE
Half-Life 2
Yep, I'm no more immune to this chatty, funny, wonderfully written lass's charms than the rest of the gaming world. Again, great writing turns what could oh so easily have been a totally forgettable cardboard cutout sidekick into realistic and relatable character. She was a triumph of female game character design, and I only wish more developers since had followed suit.
REVOLVER OCELOT
Metal Gear Solid & Metal Gear Solid 2
(Those were the only games in the series that I played). Not much to say, I just thought he was one cool (and terrifying) bastard. I remember being hugely impressed and moved as his backstory unfolded, although I can't for the life of me remember any of it. But he has an awesome coat, so there's always that.
XAN
Baldur's Gate
In my apparently very unpopular opinion, the best character in Baldur's Gate was not Minsc (not that I have anything against our muscle-bound loony ranger friend, mind) but Xan, the miserable, pessimistic, sarcastic yet somehow very likable wizard who looks and sounds like Alan Rickman. Although I never forgave him for being an enchanter and therefore not being able to learn Magic Missile thanks to the lame, old-school D&D rules. Now that was an aesthetics vs. power choice if there ever was one.
Honorary mentions for characters who just missed the list:
Red XIII, Vincent Valentine, Aeris Gainsborough, Tifa Lockheart, Auron, Kimahri, Freya, Vivi, Locke Cole, Terra Branford, Sephiroth and Kefka... Is it just me or is there some kind of common denominator here?