How to Convert String Case in PostgreSQL

Last updated on July 12th, 2024 at 06:22 am

PostgreSQL database supports a wide range of data types including strings and texts of different formats and sizes. It also provides many useful functions and features to easily work with string data types. Sometimes you may need to convert uppercase string to lowercase or lowercase string to uppercase. This is commonly required if your data is stored in a specific case and you need to display it in another case on your website or app, without modifying the actual data. In this article, we will look at how to convert string case in PostgreSQL.

How to Convert String Case in PostgreSQL

It is very easy to convert string case in PostgreSQL. We will use lower() function to convert string to lower case, and upper() function to convert string to upper case in PostgreSQL. We will also look at initcap() function that allows you to capitalize first letter of every word in a string. Please note, none of the following functions modify the actual data present in your database. They only transform the result of SQL queries.

1. Convert to Lowercase in PostgreSQL

lower() function allows you to convert string to lowercase in PostgreSQL. Please note, it will convert your string to lowercase no matter its present case. So whether your string is present as uppercase or camel case or only some of its intermediate alphabets are uppercase, it will be completely converted into uppercase.

Here’s the syntax of lower()

lower(string)

You can provide string as a literal or a column.

Here’s an example to convert literal string to lowercase in PostgreSQL.

select lower('TEST Lower');
   lower
------------
 test lower

Here is an example to convert string column to lower case.

# select first_name,lower(first_name) from employees;
 first_name | lower
------------+-------
 Jim        | jim
 TIM        | tim
 JaNe       | jane

2. Convert to Uppercase in PostgreSQL

upper() function allows you to convert string to uppercase in PostgreSQL. Like lower() function, this too will completely convert your input string to uppercase, irrespective of whether it is lowercase, or camel case or if only some of its letters are lowercase.

Here’s the syntax of upper()

upper(string)

You can provide string as a literal or a column.

Here’s an example to convert literal string to uppercase in PostgreSQL.

select upper('test upper');
   upper
------------
 TEST UPPER

Here is an example to convert string column to upper case.

# select first_name,upper(first_name) from employees;
 first_name | upper
------------+-------
 Jim        | JIM
 tim        | TIM
 jAnE       | JANE

3. Convert First Character to Uppercase

Often web developers need to capitalize only the first letter of string. This is especially required if you need to display first name and last name on your site. If you want to capitalize first letter of string then use initcap() function. initcap() function allows you to convert first character of every word in a string into uppercase making it pleasant to read.

Here is the syntax of initcap()

initcap(string)

You can use string literal or column name as input. Here is an example of initcap() to convert first character of every word in a string to uppercase.

# select initcap('test initcap');
   initcap
--------------
 Test Initcap

Please note, initcap() function will only modify the first letter of each word of your string and leave rest of the characters of the word unchanged. It will not force those alphabets to lowercase.

Here is an example to convert string column to upper case.

# select first_name,upper(first_name) from employees;
 first_name | upper
------------+-------
 jim        | Jim
 tIm        | TIm
 jANE       | JANE

Conclusion

In this article, we have learnt how to convert lowercase string to uppercase, uppercase string to lowercase and convert string to camel case. These are commonly required use cases in web development and do not require any kind of coding. You just need to simply use these functions in SQL queries. If the result of the queries in required case format, then there is no need to do any transformation via code and you can directly display them on your website. Also, these functions do not modify the underlying data. So your original data in database remains unchanged. They only transform the result.

Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Also read :
How to Concatenate Strings in PostgreSQL
How to Compare Arrays in PostgreSQL
How to Update Multiple Columns in PostgreSQL
How to Compare Arrays in PostgreSQL