String Functions in SQL

String functions are used to perform operations on text data.

They can help you:

• Convert text to uppercase or lowercase
• Find the length of text
• Combine strings
• Extract part of a string
• Remove unwanted spaces
• Replace text

Some commonly used string functions include:

The UPPER() function converts text to uppercase.

SELECT UPPER('hello');

Result:

HELLO

It can also be used with a table column.

You can use UPPER() to display a column in uppercase.

SELECT UPPER(name) AS student_name
FROM students;

This displays student names in uppercase.

The LOWER() function converts text to lowercase.

SELECT LOWER('HELLO');

Result:

hello

LOWER() can be used with table columns.

SELECT LOWER(email) AS email_address
FROM students;

This displays email addresses in lowercase.

The LENGTH() function returns the length of a string. In MySQL, LENGTH() returns the number of bytes, which can differ from character count for multibyte text.

SELECT LENGTH('Hello');

For this ASCII text, the result is 5.

You can calculate the length of values stored in a column.

SELECT name, LENGTH(name) AS name_length
FROM students;

This displays each name along with its length.

In MySQL, CHAR_LENGTH() returns the number of characters in a string.

SELECT CHAR_LENGTH('Hello');

Result:

5

CHAR_LENGTH() is useful when character count is needed instead of byte count.

The CONCAT() function combines two or more strings.

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

Result:

Hello World

CONCAT() can combine values from different columns.

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

This combines the first name and last name.

In MySQL, CONCAT_WS() combines strings using a separator.

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

The first argument is the separator.

The TRIM() function removes leading and trailing spaces.

SELECT TRIM('  Hello  ');

Result:

Hello

TRIM() is useful when stored text contains unwanted spaces.

SELECT TRIM(name) AS clean_name
FROM students;

This removes spaces from the beginning and end of each name.

In MySQL, LTRIM() removes leading spaces from a string.

SELECT LTRIM('   Hello');

Result:

Hello

Trailing spaces are not the focus of LTRIM().

In MySQL, RTRIM() removes trailing spaces from a string.

SELECT RTRIM('Hello   ');

Result:

Hello

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

SELECT SUBSTRING('Hello World', 1, 5);

Result:

Hello

In MySQL, the starting position is 1-based.

You can extract part of a value stored in a column.

SELECT SUBSTRING(name, 1, 3) AS short_name
FROM students;

This extracts the first three characters of each name.

In MySQL, LEFT() returns a specified number of characters from the beginning of a string.

SELECT LEFT('Computer', 4);

Result:

Comp

In MySQL, RIGHT() returns a specified number of characters from the end of a string.

SELECT RIGHT('Computer', 4);

Result:

uter

The REPLACE() function replaces occurrences of one substring with another.

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

Result:

I like Python

REPLACE() can be used to modify displayed text without changing the stored data.

SELECT REPLACE(course, 'Java', 'Python') AS course_name
FROM students;

This changes the displayed result of matching text.

In MySQL, LOCATE() finds the position of a substring inside another string.

SELECT LOCATE('SQL', 'Learn SQL Today');

The result is the starting position of SQL.

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

SELECT REVERSE('SQL');

Result:

LQS

You can use different string functions depending on how you want to format text.

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

This displays both uppercase and lowercase versions.

String functions can also be used in filtering conditions.

SELECT *
FROM students
WHERE LOWER(course) = 'python';

This compares the course value after converting it to lowercase.

String functions can be used while sorting results.

SELECT name
FROM students
ORDER BY LOWER(name);

This sorts names using their lowercase values.

Many string functions return NULL when their input expression is NULL.

SELECT UPPER(NULL);

The result is NULL.

When working with nullable columns, consider using functions such as COALESCE() when you need a replacement value.

String functions can be used to format student information.

SELECT
    UPPER(name) AS student_name,
    LOWER(email) AS email_address,
    LENGTH(name) AS name_length,
    CONCAT(name, ' - ', course) AS student_course
FROM students;

This creates a formatted student report using several string functions.

Here is a complete example using several commonly used string functions.

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

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(first_name) AS uppercase_name,
    LOWER(email) AS email_address,
    LENGTH(first_name) AS name_length,
    LEFT(course, 3) AS course_code,
    TRIM(course) AS clean_course
FROM students;

This query combines names, changes text case, calculates string length, extracts characters, and removes unnecessary spaces.