I have an Injen CAI. It has a slight whistle type noise as do most aftermarket intakes I've seen. Injen includes the CA carb sticker so if a cop stops you and checks under the hood, the carb CA stick will send em away - trust me, I learned the hard way. AEM are carb legal as well. As far as noise, sounds a little more slightly more aggressive than stock under 3k rpm but when vtec kicks in, it'll kick butt!
my aem sri makes a whistling sound from 2100 to 3000, and then a little over 4000 and stops at 4200. just open the throttle more than a quarter, and it whistles. kinda cool...
I have a Weapon-R Secret Weapon short ram and it's definatly mean sounding. My buddy, whom has a Corvette eating Eagle Talon, commented on how mean it was when I drove by him.
Maybe this will help. With a CAI it is true that you draw in more cold air vs. a SRI. The downside about the CAI, if it is not one like the one AEM sold where you can buy a filter that would help prevent a little water from being sucked into the engine (rainy conditions, not pools of water) hydrolock. Also a negative, is that eventhough the air is considered to be more condensed do to the fact that it drawn from under the car, it is lost do to all of the bends that the air must travel through. I personally opted to go with an SRI and plan on building a heatshield for it this spring. It is all personal preference.