If you have Google Messages (you can probably do the same with any other SMS application) you can set sound on for it's notifications and disable the sound for any other notification, if this is what you want. You just have to config the notifications by app.