Ok, for the main setting of Notifications go to Settings > Sound > Notification Sound (located under the header Notification) and you should see a list of sounds. If the sound you're looking for isn't on this list, let me know. I don't now where the sound got stored on your phone when you downloaded it. Was it downloaded through an app like Zedge?
Correction... Just remembered you wanted custom settings so do this which will allow you to have a different sound for each of the Message types.
For specific text sounds, go to Messages > Settings > Notifications > Notification sound (located under the heading Received messages) and you should see a list of sounds here too. This setting, will change the sounds only for Message notifications (i.e. Text, MMS, Sent Messages, Voicemail notification) - there's a separate heading for each of those message types.