MySQL LEFT() and RIGHT() Functions

By /
LEFT Function

In this tutorial, we will learn about the MySQL LEFT() and RIGHT() functions. The LEFT() function is a string function that is used to extract a given number of characters from a string, starting from the left. For instance, you have a string with the value “JournalDev” and you want the first 7 characters from the left. On passing these values in the LEFT() function, you will get your output as “Journal”.

The RIGHT() function is a string function that is used to extract a given number of characters from a string, starting from the right. For instance, you have a string with the value “JournalDev” and you want the last 3 characters from the right. On passing these values in the RIGHT() function, you will get your output as “Dev”.

The LEFT() and the RIGHT() functions are multibyte safe and are usually used with the SELECT statement.

Syntax of MySQL LEFT()

LEFT(string, number_of_characters);Code language: SQL (Structured Query Language) (sql)

Where ‘number_of_characters’ are the number of characters you want to extract from the left of the ‘string’.

Syntax of MySQL RIGHT()

RIGHT(string, number_of_characters);Code language: SQL (Structured Query Language) (sql)

Where ‘number_of_characters’ are the number of characters you want to extract from the right of the ‘string’.

Examples of MySQL LEFT()

Now, let’s go over some of the examples of the LEFT() function.

Basic Examples

Let us consider the below queries. We use an alias called Result to make our output readable.

SELECT LEFT('JournalDev', 7) AS Result; 
SELECT LEFT('JournalDev', NULL) AS Result; 
SELECT LEFT('JournalDev', -2) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL Left Basic Example

Using a NULL value as the number of characters returns a NULL string. If we use 0 or any negative number as the number of characters, we get an empty string.

Using Table Data

Let us work with some table data now. We will extract the first three characters from every department name and give that output column an alias called Department_Code. We will make use of the DISTINCT keyword so that we do not see repetitive values.

SELECT DISTINCT Department, LEFT(Department, 3) AS Department_Code FROM Employee;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

MySQL Left Row Values Example
MySQL Left example

Examples of MySQL RIGHT()

Time to demonstrate how the RIGHT() function works.

Basic Examples

Let us consider the below queries. We use an alias called Result to make our output readable.

SELECT RIGHT(‘Tom Sawyer’, 3) AS Result; 
SELECT RIGHT(‘Tom Sawyer’, NULL) AS Result; 
SELECT RIGHT(‘Tom Sawyer’, -2) AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL Right Basic Example

Using a NULL value as the number of characters returns a NULL string. If we use 0 or any negative number as the number of characters, we get an empty string.

Using Table Data

Let us work with some table data now. We will extract the last five characters from every department name and give that output column an alias called Result. We will make use of the DISTINCT keyword so that we do not see repetitive values.

SELECT DISTINCT Department, RIGHT(Department, 5) AS Result FROM Employee;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

MySQL Right Row Example

Conclusion

MySQL LEFT() and RIGHT() functions are very useful string functions that can be used to extract data from columns. I would encourage you to have a look at the below references.

References