No personal experience, but I have been told by an exhaust specialist that the cat tends to filter out the high frequency noise, creating a deeper sound. Removing the cat will do the opposite: more noise, but less deep. Very different from the effect of a sports exhaust!
It depends on the metal and setup of your replacement pipe. I've heard two homemade versions, one 2.0litre 8 valve (Shug) and a 2.0litre 16 valve (Jason77).
Shugs is thick stainless steel and accentuates that 8 valve rasp. Sounds really nice and not any louder really.
Jasons I think was not Stainless and possibly a bit thinner guage. Again no louder, but his car ended up with a resonance at about 4500rpm which sounded like a high pitched "Zing". I personally thought it sounded OK, but J didn't like it, so put the cat back on.
I de cated my 8v, and replaced it with a steel tube the same thickness as the original exhaust. The sound is more mature(deeper) all the way up the revs, before when you passed 4000rpm, the car sound was much higher like a whine almost.