How to convert the first letter of each word to UPPERcase except stop words with PostgreSQL

Last week, I had to convert the first letter of each word to uppercase with PostgreSQL. PG has a very useful builtin function to do it: initcap(). However, it modifies every word of the string or column without flexibility. In my case, I had to convert all of them except some stop words, so initcap() was not enough.

Here is what I did. I’m sharing it because I’ve found a lot of very too long and complicated functions on google. I prefer to keep it simple.

It’s straightforward.

  1. Convert everything with the initcap() function
  2. Create an array of words you don’t want to be converted and for each element,  reconvert to lowercase the word with the value from the array with the replace() function.

You need to be careful to not replace part of a longer word, like “for” and “fortune“. In my example, the words were easy to identify because there were delimited by spaces. You can use a the regexp_replace() function to make sure that only the stop words are replaced if you don’t have obvious delimiters.