In SQL, capitalizing the first letter of a string can be essential for formatting data or presenting it in a standardized way. Whether you’re dealing with names, titles, or any textual data, knowing how to modify the case of letters is a fundamental skill. This guide explores how to capitalize the first letter of a string in SQL, using functions like UPPER(), LOWER(), and SUBSTRING() in MySQL, ensuring your data is presented correctly and professionally.

Steps to Capitalize First Letter in SQL

Step 1: Create the Database

To begin, let’s create a database named “GEEKSFORGEEKS.”

CREATE DATABASE GEEKSFORGEEKS;

Output: After executing the above command, observe the schemas window on the left updated with the “GEEKSFORGEEKS” database.

Step 2: Use the Database

Next, switch to the “GEEKSFORGEEKS” database for further operations.

USE GEEKSFORGEEKS

 Output: Now we are using the “GEEKSFORGEEKS” database.

Step 3: Create the Student Table

Create a table named “student” with columns std_id (integer) and name (varchar(40)), where std_id is the primary key.

CREATE TABLE IF NOT EXISTS Student (
    std_id INT PRIMARY KEY,
    name VARCHAR(40)
);
TRUNCATE TABLE Student;

Student table:

Output

The “student” table is created successfully with std_id as the primary key.

Step 4: Insert Rows into the Student Table

Insert three rows of data into the “student” table.

INSERT INTO Student (std_id, name) VALUES ('1', 'jaNU');
INSERT INTO Student (std_id, name) VALUES ('2', 'bOB');
INSERT INTO Student (std_id, name) VALUES ('3', 'bINdu');

Output: Three rows are inserted into the “Student” table with lowercase names.

Step 5: Display All Rows from the Student Table

Retrieve and display all rows from the “student” table.

SELECT * FROM Student;

Output:

+--------+-------+
| std_id | name  |
+--------+-------+
|      1 | jaNU  |
|      2 | bOB   |
|      3 | bINdu |
+--------+-------+

Step 6: Format Names with First Letter Uppercase

Retrieve student names from the “student” table with the first letter capitalized and the rest in lowercase using SQL string functions.

Query:

SELECT 
    std_id,
    CONCAT(
        UPPER(SUBSTRING(name, 1, 1)),
        LOWER(SUBSTRING(name, 2, LENGTH(name)))
    ) AS capitalized_name
FROM Student  
ORDER BY std_id ASC;

Output:

+--------+------------------+
| std_id | capitalized_name |
+--------+------------------+
|      1 | Janu             |
|      2 | Bob              |
|      3 | Bindu            |
+--------+------------------+

Finally, we can see the names of the student in the student table with the first character being upper and the rest are lower. 

Transforming First Letter Capitalization in SQL

1. To capitalize the first letter of each string in a column named name, we use the LEFT(name, 1) function to extract the first character of each string. The UPPER() function is then applied to convert this first character to uppercase.
2. Similarly, for the rest of the string, the RIGHT(name, LENGTH(name) - 1) function is used. This extracts all characters except the first one, and the LOWER() function converts these characters to lowercase.

• LEFT(): Extracts characters from the beginning (left) of a string.
• RIGHT(): Extracts characters from the end (right) of a string.
• UPPER(): Converts all letters in a string to uppercase.
• LOWER(): Converts all letters in a string to lowercase.
• CONCAT(): Concatenates multiple strings together in SQL.

These functions combined allow for modifying the case of strings effectively within SQL queries.

Conclusion

In conclusion, this article demonstrated how to capitalize the first letter of names retrieved from a SQL Server database using SQL string functions. By leveraging functions like UPPER(), LOWER(), and SUBSTRING(), SQL enables efficient manipulation of string data, ensuring consistent formatting across database records.

Capitalizing the first letter of names is essential for maintaining data consistency and presentation standards in applications and reports. SQL’s robust capabilities make it a powerful tool for data management tasks, providing flexibility and efficiency in handling text manipulation requirements within relational databases.

FAQs on How To Capitalize First Letter in SQL

What SQL functions are used to capitalize the first letter of a string?

To capitalize the first letter of a string in SQL, you can use UPPER() to convert the first character to uppercase and LOWER() to convert the rest of the characters to lowercase.

Can this method handle names with special characters or numbers?

Yes, UPPER() and LOWER() functions in SQL only affect alphabetical characters. Special characters and numbers remain unchanged.

Are there any limitations to using these functions?

One limitation is that SQL syntax may vary slightly between database systems (e.g., MySQL, SQL Server, PostgreSQL). Ensure compatibility when using these functions.