How to Remove the First and Last Character of a Text in SQL

1. Introduction

In SQL, string manipulation is a common and essential task used to clean, format, and standardize text data. Removing the first and last characters from a string is one such operation that usually helps to strip unwanted symbols, brackets, or formatting characters, ensuring data consistency.

In this tutorial, we’ll learn how to remove the first and last characters of a text in SQL. The examples here are based on the Baeldung University schema.

2. Using the SUBSTRING() Function

The SUBSTRING() function extracts a part of the input string from the specified position:

SELECT id, SUBSTRING(name, 2, LENGTH(name) - 2) AS new_name FROM Course;

In this query, the SUBSTRING() function removes the first and last characters from the name column.

Let’s see the results:

+-------+-------------------------------------------+
| id    | new_name                                  |
+-------+-------------------------------------------+
| CE111 | ntroduction to Structural Engineerin      |
| CE121 | eotechnical Engineering-                  |
| CE131 | echanics of Solids-                       |
| CE141 | echanics of Fluids-                       |
...
| ME421 | anotechnology-II                          |
| ME431 | dvanced Operations Managemen              |
+-------+-------------------------------------------+
81 rows in set (0.01 sec)

Furthermore, let’s compare the name column before and after the removal of the first and last characters:

SELECT id, name, SUBSTRING(name, 2, LENGTH(name) - 2) AS new_name FROM Course;

This query results in three columns:

1. id
2. name
3. new_name

The name column shows the original course names, while the new_name column shows the names after removing specific characters.

The LENGTH() function works in MySQL and PostgreSQL.

In SQL Server, we can use LEN() instead of LENGTH():

SELECT id, SUBSTRING(name, 2, LEN(name) - 2) AS new_name FROM Course;

Otherwise, the syntax is basically the same.

3. Using the LEFT() and RIGHT() Functions

We can use LEFT() and RIGHT() to extract a specified number of characters from the beginning and end of a string, respectively.

Let’s see it in action:

SELECT id, name, LEFT(RIGHT(name, LENGTH(name) - 1), LENGTH(name) - 2) as new_name FROM Course;
+-------+---------------------------------------------+-------------------------------------------+
| id    | name                                        | new_name                                  |
+-------+---------------------------------------------+-------------------------------------------+
| CE111 | Introduction to Structural Engineering      | ntroduction to Structural Engineerin      |
| CE121 | Geotechnical Engineering-I                  | eotechnical Engineering-                  |
| CE131 | Mechanics of Solids-I                       | echanics of Solids-                       |
...
| ME431 | Advanced Operations Management              | dvanced Operations Managemen              |
+-------+---------------------------------------------+-------------------------------------------+
81 rows in set (0.01 sec)

In this example, RIGHT(name, LENGTH(name) – 1) removes the first character from the name column, while the outer LEFT() function removes the last character from the result and returns the final output in the new_name column.

Since we’re removing one character from each side of the text, we can achieve the same results even if the LEFT() and RIGHT() functions are swapped:

SELECT id, name, RIGHT(LEFT(name, LENGTH(name) - 1), LENGTH(name) - 2) as new_name FROM Course;

Again, we run this query in SQL Server by replacing the LENGTH() with LEN().

4. Handling Edge Cases

Considering edge cases when removing characters from text is crucial to avoid errors or unexpected results.

For instance, the string might be too short or may not contain the intended characters to be removed.

4.1. Checking Length

We can check the length of the text before removing any characters, and only remove characters from strings that are longer than a specified length:

SELECT id, name, CASE 
    WHEN LENGTH(name) > 13 
    THEN SUBSTRING(name, 2, LENGTH(name) - 2) 
    ELSE name 
END AS new_name, LENGTH(name) AS name_length FROM Course ORDER BY name_length;
+-------+---------------------------------------------+-------------------------------------------+-------------+
| id    | name                                        | new_name                                  | name_length |
+-------+---------------------------------------------+-------------------------------------------+-------------+
| CS235 | Planar Graphs                               | Planar Graphs                             |          13 |
| EC131 | Electronics-I                               | Electronics-I                             |          13 |
| EC231 | Electronics-II                              | lectronics-I                              |          14 |
...
| CS112 | Introduction to Real Time Operating Systems | ntroduction to Real Time Operating System |          43 |
+-------+---------------------------------------------+-------------------------------------------+-------------+
81 rows in set (0.00 sec)

This query checks the length of the name column in a CASE statement. It removes the first and last characters only if the length of the name is greater than a specified value (13). Otherwise, it returns the original name unchanged.

4.2. Remove Only Specific Character

In addition to checking string length, we can verify whether certain characters exist at specific positions (i.e., first or last) and remove them only when those conditions are met.

For example, let’s remove the character M from the beginning and the character I from the end of the string, but only if they exist in those positions:

SELECT CASE 
    WHEN LEFT(name, 1) = 'M' AND RIGHT(name, 1) = 'I' 
    THEN SUBSTRING(name, 2, LENGTH(name) - 2) 
    ELSE name 
END AS new_name FROM Course;
+----------------------------------------+
| new_name                               |
+----------------------------------------+
| Introduction to Structural Engineering |
| Geotechnical Engineering-I             |
| echanics of Solids-                    |
| echanics of Fluids-                    |
...
+----------------------------------------+
81 rows in set (0.00 sec)

This query checks whether the values in the name column start with the character M and end with the character I. If both conditions are true, it removes the first and last characters using the SUBSTRING() function. Otherwise, it returns the original value from the name column.

Such an additional check can be invaluable when dealing with specific formatting and punctuation artifacts.

5. Conclusion

In this article, we learned different ways to remove the first and last characters of a text in SQL.

First, we briefly discussed the SUBSTRING() function and its usage to remove characters from a text. Then, we used the combination of LEFT() and RIGHT() functions to remove characters from both ends of a string. Finally, we considered some edge cases, such as shorter strings and removing only specific characters from each end of the string.