Remove the Last Two Characters From a String Column in SQL

1. Overview

When working with SQL databases, we may need to modify or clean up data stored in string columns. Whether we’re removing characters or trimming extra spaces, SQL provides us with functions we can use to manipulate strings.

In this tutorial, we’ll explore various approaches to remove the last two characters from a string column in MySQL, PostgreSQL, and SQL Server using the Baeldung University database schema.

2. Problem Statement

We focus on the Departments table which contains the name and code of each department:

+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  0 | Data Science                   | CS   |
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
...

Here, we modify the name column by removing the last two characters from each Department name.

In SQL databases, we can use functions such as SUBSTRING(), LEFT(), and RTRIM() to manipulate strings. The syntax for removing characters can vary slightly depending on the database but the logic remains the same.

3. Using MySQL

In MySQL, we can use either the LEFT() or the SUBSTRING() functions to remove characters from a string.

The LEFT() function enables us to extract a substring starting from the left side of the string with a length up to a specified number of characters.

So, we remove the last two characters from a string, by subtracting 2 from the total length of the string:

SELECT
  LEFT(name, LENGTH(name) - 2) AS modified_name
FROM Departments;

In this example, LEFT(name, LENGTH(name) – 2) extracts all but the last two characters from each value in the name column:

+------------------------------+
| modified_name                |
+------------------------------+
| Data Scien                   |
| Computer Scien               |
| Electronics and Communicatio |
| Mechanical Engineeri         |
...

Alternatively, we can use the SUBSTRING() function. It provides a way to specify the starting position and the number of characters to extract:

SELECT
  SUBSTRING(name, 1, LENGTH(name) - 2) AS modified_name
FROM Departments;

In the above query, we use SUBSTRING(name, 1, LENGTH(name) – 2) to extract a substring from the first character up to the length of the name minus 2 characters.

4. Using PostgreSQL

In PostgreSQL, we use the SUBSTRING function to remove the last two characters:

SELECT 
  SUBSTRING(name FROM 1 FOR LENGTH(name) - 2) AS modified_name
FROM Departments;

This query extracts a substring from the name column, starting from the first character up to two characters before the end of the name removing the last two characters.

5. Using SQL Server

SQL Server also supports string manipulation through the SUBSTRING() and LEFT() functions:

SELECT 
  SUBSTRING(name, 1, LEN(name) - 2) AS modified_name 
FROM Departments;

Above, SUBSTRING(name, 1, LEN(name) – 2) extracts a substring from the name column starting from the first character and returning all characters except the last two. Here, LEN(name) calculates the length of the value in the name column for each row while LEN(name) – 2 reduces the length by 2.

Additionally, we can also make use of the LEFT() function:

SELECT 
  LEFT(name, LEN(name) - 2) AS modified_name 
FROM Departments;

Here, we also leverage the LEN() function to calculate the length of the string; then we effectively remove the last two characters from the string.

6. Handling Edge Cases

When removing characters from a string, we should consider edge cases. For instance, if the string’s length is less than or equal to two characters, the above functions return an empty string.

To avoid this issue, we can modify the query by adding conditions:

SELECT CASE 
    WHEN LENGTH(name) > 2 THEN LEFT(name, LENGTH(name) - 2)
    ELSE name 
END AS modified_name 
FROM Departments;

The query above ensures that if a string has less than or is equal to 2 characters, it remains unchanged.

Furthermore, the query works well as is in MySQL and PostgreSQL. To use it In SQL Server, we replace the LENGTH(name) function with LEN(name).

7. Conclusion

In this article, we discussed removing the last two characters from a string column in the MySQL, PostgreSQL, and SQL Server databases.

Additionally, we also looked at handling edge cases such as strings containing less than two characters.