I am using a Sony DSC N1 camera, while it is pretty amazing at taking day time pictures - it fails miserably when it comes to take night shots, the shots are blurred. I want a camera that is able to take day-time-like night scenes without the need of tripod or the red light that helps the camera to focus.

I think what you want is almost impossible.
If it's night, and it's dark, then you need to have the shutter open for longer.
That makes blurred images without a tripod.

Even a Digital SLR costing thousands can't break the laws of physics.
What you will get with a DSLR is better response in low light and less noise in the picture.
But that's only "better" and "less" it just don't be the same as in daylight.

You need a camera that has a high ISO speed, that means it's more sensitive to the little light that's about at night.
But if the camera is a point and shoot pocket camera, then the sensor is small which isn't good. Small sensor means tiny pixels each of which gathers less light, and so there is more noise relative to the image that you want.

They're getting better, and I think manufacturers are going have to to concentrate more on image quality (eventually) instead of just counting megapixels. If you want to take a lot of photographs at night, then consider a camera with less megapixels.
Bigger pixels will have better light gathering, and less noise, assuming the lens and the size of the sensor stay the same when comparing different cameras.

I have a friend who is using a DSLR and many of his night pics come out quite clear even without a tripod.

This picture for example (from http://www.graffitiresearchlab.com/)
http://farm1.static.flickr.com/134/379057380_7d2c35967c.jpg

It is sharp and clear, it is this type of images I want.

Have to learn a little bit about photography - if the shutterspeed is slow enough you're going to get blurry pictures hand held. Just the way it works.

I have a friend who is using a DSLR and many of his night pics come out quite clear even without a tripod.
It is sharp and clear, it is this type of images I want.

So that is the answer, if you can afford it, get a DSLR.
If you can't afford it, then you're probably not going to get results like his.

There are P&S cameras coming out now with ISO speeds up around 1600 but the noise is still high.

This is a full resolution crop from a Panasonic LZ5, ISO 1600, but as you can see low quality.
Can you even tell it's a rabbit?

Yes I can afford a DSLR but find them quite inconvenient but since P&S cams can't offer what I want, I guess i'll have to go with a DSLR.

Yes the Panasonic LZ5 still has trouble with night scenes. Is that a rabbit?