How to Convert Strings to Lowercase in MySQL

Convert Strings to Lowercase in MySQL

In MySQL, you can use LOWER() function to convert any string to lowercase, which can be applied to both static text and data stored in table columns.

Converting strings to lowercase is often helpful for ensuring consistent data formatting in SQL databases. For instance, lowercase strings are commonly used in email addresses, usernames, and case-insensitive searches.

---

Using the LOWER() Function in MySQL

The LOWER() function in MySQL takes a string input and returns that string with all characters converted to lowercase. The syntax for using LOWER() is straightforward:

LOWER(string)

Here, string can be a literal text, a column value, or any expression that evaluates to a string. Let’s set up an example table and use the LOWER() function in several examples to demonstrate how it works.

---

Setting Up an Example Table

To demonstrate the LOWER() function, we’ll create a sample customers table with fields for username, email, and city. These fields will allow us to explore various use cases for converting text to lowercase.

1. Create a new database called app_data:

CREATE DATABASE app_data;

2. Switch to the app_data database:

USE app_data;

3. Create a customers table with fields username, email, and city:

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    city VARCHAR(50)
);

4. Insert sample data into the customers table:

INSERT INTO customers (username, email, city)
VALUES ('JohnDoe', 'JOHNDOE@EXAMPLE.COM', 'NEW YORK'),
       ('JaneSmith', 'JANESMITH@EXAMPLE.COM', 'Los Angeles'),
       ('CharlieBrown', 'CHARLIEBROWN@EXAMPLE.COM', 'Chicago');

With our sample data ready, let’s look at examples of using LOWER() to convert text to lowercase in MySQL.

---

Examples: Using LOWER() in MySQL Queries

Below are different ways to use the LOWER() function in SQL queries for converting text to lowercase in MySQL.

---

1. Converting Usernames to Lowercase

To display each username in lowercase format:

SELECT username, LOWER(username) AS lowercase_username
FROM customers;

This query returns each username in lowercase, making the output consistent, which is especially useful for login or display purposes.

---

2. Converting Email Addresses to Lowercase

To ensure all email addresses are displayed in lowercase:

SELECT email, LOWER(email) AS lowercase_email
FROM customers;

This query displays each email in lowercase, which is ideal for case-insensitive email comparisons or consistent formatting in reports.

---

3. Using LOWER() for Case-Insensitive Searches

To search for customers in “new york” regardless of the case stored in the city field:

SELECT *
FROM customers
WHERE LOWER(city) = 'new york';

This query matches all entries with “new york” in any case (e.g., “NEW YORK”, “New York”) by converting both sides of the comparison to lowercase.

---

4. Using LOWER() with CONCAT for Custom Output

To display each username in lowercase with a prefix for custom formatting:

SELECT CONCAT('User: ', LOWER(username)) AS formatted_username
FROM customers;

This query returns each username in lowercase with the prefix “User: “, like “User: johndoe”, which is useful for personalized reports or displays.