Propercase in SQL: Practical Guide

Propercase, also known as title case, is a text formatting style where the first letter of each word is capitalized.

While it might seem like a simple concept, implementing propercase in SQL can be tricky due to the lack of a built-in function in most database systems.

This article will guide you through various methods to achieve propercase in SQL, complete with practical examples and real-world applications.

Understanding Propercase

Before we dive into SQL implementations, let’s clarify what we mean by propercase:

• The first letter of each word is capitalized
• All other letters are lowercase
• Some words (like “a”, “an”, “the”) might remain lowercase, depending on style rules

For example, “the quick brown fox” becomes “The Quick Brown Fox” in propercase.

Basic Propercase Implementation in SQL

Let’s start with a simple implementation that capitalizes the first letter of each word:

SELECT 
    CONCAT(
        UPPER(LEFT(column_name, 1)),
        LOWER(SUBSTRING(column_name, 2))
    ) AS propercase_column
FROM 
    your_table;

This query does the following:

1. LEFT(column_name, 1) extracts the first character
2. UPPER() capitalizes it
3. SUBSTRING(column_name, 2) gets the rest of the string
4. LOWER() converts it to lowercase
5. CONCAT() joins these parts

While this works for single words, it falls short for multi-word strings. Let’s tackle that next.

Multi-Word Propercase in SQL

For multi-word strings, we need to split the string, apply our propercase logic, and then recombine. Here’s how we can do it in SQL Server:

CREATE FUNCTION dbo.ProperCase(@Text AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @Reset BIT;
    DECLARE @Ret VARCHAR(8000);
    DECLARE @i INT;
    DECLARE @c CHAR(1);

    SELECT @Reset = 1, @i = 1, @Ret = '';

    WHILE (@i <= LEN(@Text))
    SELECT @c = SUBSTRING(@Text, @i, 1),
           @Ret = @Ret + CASE WHEN @Reset = 1 THEN UPPER(@c) ELSE LOWER(@c) END,
           @Reset = CASE WHEN @c LIKE '[a-zA-Z]' THEN 0 ELSE 1 END,
           @i = @i + 1
    RETURN @Ret
END

This function iterates through each character, capitalizing the first letter of each word and lowercasing the rest. You can use it like this:

SELECT dbo.ProperCase('the quick brown fox') AS propercase_text;

Output: “The Quick Brown Fox”

Handling Exceptions in Propercase

In real-world scenarios, you might want to keep certain words lowercase. Let’s modify our function to handle this:

CREATE FUNCTION dbo.ProperCaseWithExceptions(@Text AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @Reset BIT;
    DECLARE @Ret VARCHAR(8000);
    DECLARE @Word VARCHAR(100);
    DECLARE @Exceptions TABLE (Word VARCHAR(20));

    INSERT INTO @Exceptions VALUES ('a'),('an'),('the'),('in'),('of'),('to');

    SELECT @Reset = 1, @Ret = '';

    WHILE LEN(@Text) > 0
    BEGIN
        SET @Word = LEFT(@Text, ISNULL(NULLIF(CHARINDEX(' ', @Text), 0), LEN(@Text)));
        SET @Text = SUBSTRING(@Text, LEN(@Word) + 2, 8000);

        IF @Word NOT IN (SELECT Word FROM @Exceptions) OR @Reset = 1
            SET @Word = UPPER(LEFT(@Word, 1)) + LOWER(SUBSTRING(@Word, 2, LEN(@Word)));
        ELSE
            SET @Word = LOWER(@Word);

        SET @Ret = @Ret + @Word + ' ';
        SET @Reset = 0;
    END

    RETURN RTRIM(@Ret);
END

This enhanced version keeps specified words lowercase unless they’re at the beginning of the string.

Real-World Application: Standardizing Product Names

Let’s apply our propercase function to a real-world scenario: standardizing product names in an e-commerce database.

Imagine you have a table Products with inconsistently formatted names:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100)
);

INSERT INTO Products VALUES 
(1, 'DELUXE COFFEE MAKER'),
(2, 'stainless steel water bottle'),
(3, 'Organic green TEA'),
(4, 'MEMORY foam PILLOW');

We can use our propercase function to standardize these names:

UPDATE Products
SET ProductName = dbo.ProperCaseWithExceptions(ProductName);

SELECT * FROM Products;

Output:

ProductID | ProductName
1         | Deluxe Coffee Maker
2         | Stainless Steel Water Bottle
3         | Organic Green Tea
4         | Memory Foam Pillow

This standardization improves data consistency and readability, which is crucial for product displays, search functionality, and reporting.

SQL Propercase with a Rapid Database Builder

When working with SQL, executing efficient queries is straightforward, but sometimes more complex operations, like handling proper casing of text data, require extra attention.

This is where rapid database builders like Five come into play. Five provides a MySQL database for your application and generates an automatic UI, making it easier to interact with and manage your data.

Five allows you to create interactive forms, tables, dynamic charts, comprehensive PDF reports, and dashboards that are automatically generated based on your database schema. This makes it easier to visualize and analyze the results of queries.

Additionally, Five lets you write custom JavaScript and TypeScript functions, providing the flexibility to implement custom business logic that can interact with your data.

Once your application is ready, Five makes deployment easy with just a few clicks, allowing you to deploy your MySQL-based application to a secure, scalable cloud infrastructure. This lets you focus on developing, while Five handles the complexities of cloud deployment and infrastructure management.

If you’re serious about using SQL efficiently, give Five a try.

Sign up for free access to Five’s online development environment and start building your MySQL web application today.

Build Your Database In 3 Steps
Start Developing Today

Propercase in Different SQL Dialects

The implementations we’ve discussed so far are specific to SQL Server. Let’s look at how to achieve propercase in other popular SQL dialects.

MySQL

MySQL doesn’t have a built-in propercase function, but we can create one using a combination of string functions:

DELIMITER //

CREATE FUNCTION ProperCase(str VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
  DECLARE c CHAR(1);
  DECLARE s VARCHAR(255);
  DECLARE i INT DEFAULT 1;
  DECLARE bool INT DEFAULT 1;
  DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/';
  SET s = LCASE( str );
  WHILE i < LENGTH( str ) DO
    SET c = SUBSTRING( s, i, 1 );
    IF LOCATE( c, punct ) > 0 THEN
      SET bool = 1;
    ELSEIF bool=1 THEN
      IF c >= 'a' AND c <= 'z' THEN
        SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));
        SET bool = 0;
      ELSEIF c >= '0' AND c <= '9' THEN
        SET bool = 0;
      END IF;
    END IF;
    SET i = i+1;
  END WHILE;
  RETURN s;
END;
//

DELIMITER ;

You can then use this function like so:

SELECT ProperCase('the QUICK brown FOX') AS propercase_text;

PostgreSQL

PostgreSQL offers the initcap function, which provides basic propercase functionality:

SELECT initcap('the quick brown fox') AS propercase_text;

However, initcap doesn’t handle exceptions for words that should remain lowercase. For more control, you can create a custom function similar to our SQL Server example.

Performance Considerations

When working with large datasets, the performance of string manipulation functions can become a concern. Here are some tips to optimize propercase operations:

1. Indexing: If you frequently search or sort on the propercase column, consider creating a computed column with the propercase value and index it.
2. Caching: For static data, consider storing the propercase version rather than computing it on-the-fly.
3. Batch Processing: For large updates, process the data in batches to avoid long-running transactions.

Propercase in Application Logic

While implementing propercase in SQL can be useful, it’s often more efficient to handle this in your application logic. Most programming languages have built-in or easily implementable propercase functions. Consider this approach if you’re dealing with large volumes of data or complex propercase rules.

Conclusion

Propercase, while seemingly simple, can be a useful tool for data standardization and presentation. By implementing propercase in SQL, you can ensure consistency directly at the database level.

The techniques we’ve explored here – from basic implementations to handling exceptions and applying in real-world scenarios – provide a solid foundation for working with propercase in SQL. As you apply these concepts, you’ll find numerous opportunities to improve data quality and presentation in your database projects.