How to Remove the First N Characters of a Column in SQL

1. Introduction

SQL string manipulation is a key feature that enables efficient data transformation. SQL provides many functions to manipulate strings, making it easy to extract, modify, or format text data stored in columns. One of the use cases is removing the first N characters from a string or column, often necessary to strip unwanted prefixes, like country codes in phone numbers.

In this tutorial, we’ll use the Course table from the University schema to learn how to remove the first N characters of a column in SQL.

2. Model

Let’s create a copy of the target column before making any updates, ensuring the original data remains unchanged:

ALTER TABLE Course ADD id_copy varchar(10);

This command adds the column id_copy to the Course table. After that, we copy the data from the id column to the newly created column, id_copy:

UPDATE Course SET id_copy = id;

We can confirm the operation’s success by selecting the first few entries with the LIMIT clause:

SELECT id, id_copy, name FROM Course LIMIT 4;
  id   | id_copy |              name              
-------+---------+--------------------------------
 CS511 | CS511   | Distributed Systems
 CS413 | CS413   | Advanced Computer Architecture
 CS121 | CS121   | Introduction to Databases
 CS122 | CS122   | Relational Databases
(4 rows)

This confirms the duplication of data into the id_copy column.

3. Using the SUBSTRING() Function

String functions manipulate an input string and return a processed string as output. For example, the SUBSTRING() function extracts a part of the input string from the specified position.

Let’s review the syntax for SUBSTRING() function:

SUBSTRING(input_string, position, length)

where,

• input_string is the string or column from which to extract a substring
• position is the starting point from where the extraction begins
• length is the number of characters to extract from the starting position

The length parameter is optional; when omitted, the function extracts the rest of the string. The position index starts at 1. Therefore, to remove the first two (n) characters, we should set position to 3 (n+1).

Let’s utilize the SUBSTRING() function to display a column (id_copy) after removing the first two characters:

SELECT SUBSTRING(id_copy, 3, 3) from Course;
 substring 
-----------
 511
 413
 121
 122
 123
...

Now that we have confirmed the output is as expected, we can proceed to update the column:

UPDATE Course SET id_copy = SUBSTRING(id_copy, 3, 3);

This command updates all the entries in the id_copy column by removing the first two characters from each string.

Before proceeding further, let’s ensure the removal of the first two characters from each string in the id_copy column:

SELECT id, id_copy, name FROM Course;
  id   | id_copy |                    name                     
-------+---------+---------------------------------------------
 CS511 | 511     | Distributed Systems
 CS413 | 413     | Advanced Computer Architecture
 CS121 | 121     | Introduction to Databases
...

Thus, we successfully removed the first two characters from the id_copy column using the SUBSTRING() function.

4. Using the RIGHT() Function

Another such function is RIGHT(), which retrieves the right part of a character string with the specified number of characters.

Let’s see it in action:

SELECT RIGHT('websitebaeldung', 8);

This results in:

baeldung

The RIGHT() function takes a string as input and extracts a specified number of characters starting from the right. We can also use this function with an entire column by providing the column name instead of an input string.

In addition, we can use the RIGHT() function with the LENGTH() function to remove the first n characters from a string or column by extracting all characters from the right after the nth character:

RIGHT(Column, LENGTH(Column) - n)

where Column is the column’s name to extract or remove characters from and n is the number of characters to remove from the beginning of the specified column.

Additionally, the LENGTH() function determines the number of characters in a given string, providing the total character count as output.

Let’s first SELECT the column by removing the first three characters:

SELECT RIGHT(id_copy, LENGTH(id_copy) - 3) from Course;
 right 
-------
 11
 13
 21
 22
 23
...

The SELECT statement selects the data but does not remove the original characters from the column; it simply displays the modified values based on the specified operation.

Likewise, we proceed with removing the characters from the column using the UPDATE statement:

UPDATE Course SET id_copy = RIGHT(id_copy, LENGTH(id_copy) - 3);

This SQL statement modifies the data in the Course table by removing the first three characters from the id_copy column.

Finally, let’s verify the updated data:

SELECT id, id_copy, name FROM Course;
  id   | id_copy |                    name                     
-------+---------+---------------------------------------------
 CS511 | 11      | Distributed Systems
 CS413 | 13      | Advanced Computer Architecture
 CS121 | 21      | Introduction to Databases
...

Thus, we successfully removed the first three characters from the id_copy column.

5. Conclusion

In this article, we learned how to remove the first N characters from a column in SQL.

To summarize, we briefly discussed the two string functions, SUBSTRING() and RIGHT(), to demonstrate the removal of the first N characters from a column.