PostgreSQL provides us with a few functions that we can use to convert strings between uppercase and lowercase.
We can convert the whole string to uppercase or lowercase, or we can convert it to initial caps, where the first letter of each word is uppercase and the rest of the word is lowercase.
The UPPER()
Function
We can use the upper()
function to convert the whole string to uppercase. We pass the string as the sole argument:
SELECT upper( 'Converting a String to Uppercase' );
Result:
CONVERTING A STRING TO UPPERCASE
Characters that were originally in lowercase have been converted to uppercase, and characters that were already uppercase remain in uppercase.
The LOWER()
Function
We can use the lower()
function to convert the whole string to lowercase. As with uppercase()
, we pass the string as the sole argument:
SELECT lower( 'Converting a STRING to Lowercase' );
Result:
converting a string to lowercase
Characters that were originally in uppercase have been converted to lowercase, and characters that were already in lowercase remain in lowercase.
The INITCAP()
Function
The initcap()
function converts the first letter of each word to uppercase and the rest to lowercase:
SELECT initcap( 'converting a STRING to initial caps' );
Result:
Converting A String To Initial Caps
PostgreSQL defines “words” as sequences of alphanumeric characters separated by non-alphanumeric characters.
Here’s another example to demonstrate some variations:
\x
SELECT
initcap( 'run4your...life' ) AS "1",
initcap( 'run-4-your...life' ) AS "2",
initcap( 'run,4,your(life)' ) AS "3",
initcap( 'run.for.yourLIFE' ) AS "4";
Result (in vertical output):
1 | Run4your...Life
2 | Run-4-Your...Life
3 | Run,4,Your(Life)
4 | Run.For.Yourlife