NUHMAN.com

When working with strings in a PostgreSQL database, you may need to convert uppercase text into Pascal Case. Pascal Case is a format where the first letter of each word is capitalized, and all words are concatenated without spaces. For example, "THIS IS A STRING" becomes "ThisIsAString".

In this article, we'll show you how to create a PostgreSQL function that can convert any uppercase string into Pascal Case.

🧱 Solution Overview

We will leverage a combination of PostgreSQL string functions:

1. lower(): Converts the entire string to lowercase.
2. initcap(): Capitalizes the first letter of each word.
3. regexp_replace(): Removes spaces between words.

🔨 Creating the Function

Here's the SQL function that converts an uppercase string to Pascal Case:

sql

CREATE OR REPLACE FUNCTION to_pascal_case(text)
RETURNS text AS $$
BEGIN
  RETURN regexp_replace(initcap(lower($1)), '\s', '', 'g');
END;
$$ LANGUAGE plpgsql;

Explanation:

• lower($1): Converts the entire string to lowercase.
• initcap(): Capitalizes the first letter of each word.
• regexp_replace(): Removes spaces from the result using a regular expression (\s for spaces) and replaces them with nothing ('').

🧑‍💻 Example Usage

Now, let’s use the function in a SQL query to convert an uppercase string:

sql

SELECT to_pascal_case('THIS IS AN EXAMPLE STRING');

Output:

nginx

ThisIsAnExampleString

This query converts the string "THIS IS AN EXAMPLE STRING" into "ThisIsAnExampleString" in Pascal Case.

🔧 Alternative Approach: Direct Query Without Function

If you don’t want to create a custom function, you can directly use the query without defining a function:

sql

SELECT regexp_replace(initcap(lower('THIS IS AN EXAMPLE STRING')), '\s', '', 'g') AS pascal_case_string;

This query will give the same result as the previous one, converting the uppercase string to Pascal Case in one step.

📊 Use Cases

This method is useful when:

• Storing or displaying data in a user-friendly format (e.g., for file names or programmatically generated identifiers).
• Converting mixed-case or uppercase database values into a standardized format.

🧠 Conclusion

By combining PostgreSQL's string manipulation functions, you can easily convert uppercase strings into Pascal Case for improved readability or standardized formatting. Whether you create a function for reusability or use a direct query, this approach helps in transforming data seamlessly.