MySQL String Functions

String functions are MySQL functions that perform operations on text values.

SELECT UPPER('hello');

Result:

HELLO

String functions can be used with both fixed text and table columns.

The UPPER() function converts all letters in a string to uppercase.

SELECT UPPER('hello world');

Result:

HELLO WORLD

Example with a column:

SELECT UPPER(name)
FROM students;

The LOWER() function converts all letters in a string to lowercase.

SELECT LOWER('HELLO WORLD');

Result:

hello world

Example:

SELECT LOWER(email)
FROM students;

The LENGTH() function returns the length of a string in bytes.

SELECT LENGTH('MySQL');

Result:

5

For ordinary English characters, the byte length is normally the same as the number of characters.

The CHAR_LENGTH() function returns the number of characters in a string.

SELECT CHAR_LENGTH('Hello');

Result:

5

Unlike LENGTH(), CHAR_LENGTH() counts characters rather than bytes.

The CONCAT() function joins two or more strings.

SELECT CONCAT('Hello', ' ', 'World');

Result:

Hello World

Example:

SELECT CONCAT(first_name, ' ', last_name)
FROM students;

CONCAT_WS() means CONCAT With Separator. It joins strings using a specified separator.

SELECT CONCAT_WS('-', '2026', '09', '21');

Result:

2026-09-21

Example:

SELECT CONCAT_WS(' ', first_name, last_name)
FROM students;

The TRIM() function removes leading and trailing spaces from a string.

SELECT TRIM('   Hello World   ');

Result:

Hello World

TRIM() is useful for cleaning text data.

The LTRIM() function removes spaces from the beginning of a string.

SELECT LTRIM('   Hello');

Result:

Hello

The spaces at the right side remain unchanged.

The RTRIM() function removes spaces from the end of a string.

SELECT RTRIM('Hello   ');

Result:

Hello

The spaces at the beginning remain unchanged.

The LEFT() function returns a specified number of characters from the beginning of a string.

SELECT LEFT('Programming', 4);

Result:

Prog

Syntax:

LEFT(string, number_of_characters)

The RIGHT() function returns a specified number of characters from the end of a string.

SELECT RIGHT('Programming', 3);

Result:

ing

Syntax:

RIGHT(string, number_of_characters)

The SUBSTRING() function extracts a portion of a string.

SELECT SUBSTRING('Programming', 1, 4);

Result:

Prog

Syntax:

SUBSTRING(string, start, length)

MySQL string positions normally start at 1.

SUBSTR() is a synonym for SUBSTRING().

SELECT SUBSTR('Database', 1, 4);

Result:

Data

Both functions can be used to extract part of a string.

The INSTR() function returns the position of the first occurrence of a substring.

SELECT INSTR('Hello World', 'World');

Result:

7

If the substring is not found, the result is 0.

The LOCATE() function searches for a substring inside another string and returns its position.

SELECT LOCATE('World', 'Hello World');

Result:

7

Syntax:

LOCATE(substring, string)

The REPLACE() function replaces all occurrences of a substring with another string.

SELECT REPLACE('I like Java', 'Java', 'Python');

Result:

I like Python

Syntax:

REPLACE(string, from_string, to_string)

The REVERSE() function reverses the characters in a string.

SELECT REVERSE('MySQL');

Result:

LQSyM

It can be useful for certain text-processing tasks.

The REPEAT() function repeats a string a specified number of times.

SELECT REPEAT('Hi ', 3);

Result:

Hi Hi Hi 

Syntax:

REPEAT(string, count)

The LPAD() function adds characters to the left side of a string until it reaches a specified length.

SELECT LPAD('123', 5, '0');

Result:

00123

Syntax:

LPAD(string, length, pad_string)

The RPAD() function adds characters to the right side of a string until it reaches a specified length.

SELECT RPAD('123', 5, '0');

Result:

12300

Syntax:

RPAD(string, length, pad_string)

The FORMAT() function formats a number with a specified number of decimal places and locale-aware separators.

SELECT FORMAT(1234567.89, 2);

A typical result is:

1,234,567.89

This function is useful when displaying numeric values as formatted text.

The FIELD() function returns the position of a value in a list of values.

SELECT FIELD('B', 'A', 'B', 'C');

Result:

2

Because B is the second value in the list.

The ELT() function returns the string at a specified position in a list.

SELECT ELT(2, 'Apple', 'Banana', 'Mango');

Result:

Banana

The first argument specifies the position.

The STRCMP() function compares two strings.

SELECT STRCMP('apple', 'apple');

Result:

0

A result of 0 means the strings compare as equal. A negative or positive result indicates their ordering according to the comparison.

The ASCII() function returns the numeric value of the first character of a string.

SELECT ASCII('A');

Result:

65

It is useful when working with character codes.

The CHAR() function returns the character for specified numeric code values, subject to MySQL's character-set handling.

SELECT CHAR(65);

Result:

A

It can be useful when converting numeric character codes into text.

String functions become especially useful when working with table columns.

SELECT
    name,
    UPPER(name) AS uppercase_name,
    LOWER(name) AS lowercase_name,
    LENGTH(name) AS name_length
FROM students;

This query returns the original name along with different processed versions of it.

Multiple string functions can be combined in a single expression.

SELECT
    UPPER(TRIM(name)) AS cleaned_name
FROM students;

Here:

• TRIM() removes leading and trailing spaces.
• UPPER() converts the cleaned value to uppercase.

Another example:

SELECT CONCAT(
    UPPER(first_name),
    ' ',
    UPPER(last_name)
) AS full_name
FROM students;

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO students
(first_name, last_name, email)
VALUES
('Amit', 'Kumar', 'amit@example.com'),
('Priya', 'Singh', 'priya@example.com'),
('Rahul', 'Sharma', 'rahul@example.com');

-- Convert names to uppercase
SELECT UPPER(first_name) AS first_name
FROM students;

-- Create full name
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM students;

-- Find name length
SELECT name_length
FROM (
    SELECT
        CONCAT(first_name, ' ', last_name) AS full_name,
        CHAR_LENGTH(CONCAT(first_name, ' ', last_name)) AS name_length
    FROM students
) AS student_data;

-- Extract first four characters
SELECT LEFT(first_name, 4)
FROM students;

-- Replace text
SELECT REPLACE(email, '@example.com', '@gmail.com')
FROM students;

-- Remove unwanted spaces and convert to uppercase
SELECT UPPER(TRIM(first_name)) AS cleaned_name
FROM students;

This example demonstrates how string functions can be used for practical text processing.