Disable text message sound when in CarPlay
The easiest solution that includes this silencing of texts is the standard do not disturb while driving.

https://support.apple.com/en-us/HT208090

I also generally don’t want text messages to ring so I often just set their alert to be no sound, ever. The third option isn’t automatic, but I often do days with my entire phone in do not disturb. If I see a call, I pick it up, if not, I react when I pick up the phone and look at missed calls, voicemails and texts. Worst case, you would have to manually change either the sound or the DND settings.

Hey Siri, turn on do not disturb.
Hey Siri, turn off do not disturb.