SQL Query to Delete Last N Characters From Field

SQL (Structured Query Language) is the foundational language used for managing and manipulating relational databases. In SQL, string manipulation plays an essential role, particularly when we need to clean or modify data. One common task is removing the last N characters from a column. This operation might be necessary when dealing with unwanted trailing characters such as spaces, commas, or other special characters.

In this article, we will explain how to delete the last N characters from a column using SQL queries. We will dive into practical examples using popular SQL functions like SUBSTRING() and LEN().

SQL String Functions Used

SQL provides a range of string functions to manipulate textual data. For removing characters, we typically rely on SUBSTRING() and LEN(). To perform the required function we need the following functions:

1. SUBSTRING()

This function extracts a portion of a string starting from a specific position. By combining it with LEN(), we can easily remove characters from the end of a string or we can say it extracts a substring from the string starting at the given position for the specified length.

Syntax

SUBSTRING(string, start_position, length)

Key Terms

• String: It is a required parameter. It provides information about the string on which function is applied.
  
• start_position,: It gives the starting position of the string. It is also the required parameter.
  
• Length: It is an optional parameter. By default, it takes the length of the whole string.

Query:

SUBSTRING('geeksforgeeks', 1, 5);

Output

geeks

2. LEN()

This function returns the number of characters in a string, excluding trailing spaces in some SQL dialects like SQL Server. It’s essential when we need to know the string's length to determine how many characters to remove. This syntax is not the standard one. Syntax for returning the length of a string may vary for different server . It takes only one parameter that is the string whose length we need to find.

Query:

LEN('geeksforgeeks')

Output

13

Removing the Last N Characters

To remove the last N characters from a column, we can use the SUBSTRING() function, along with the LEN() function to specify the length of the substring. To delete the last N characters from the field we will use the following query:

Syntax

SUBSTRING(string, 1, length(string)-N)

Key Terms

• column_name: The name of the column from which you want to remove characters.
  
• LEN(column_name) - N: Calculates the new length by subtracting N characters from the total length of the column.

Step-by-Step Guide: Removing the Last N Characters in SQL

Let’s start by creating a sample table called geeksforgeeks in a database, which will help us demonstrate how to remove the last N characters from a field.

Step 1: Create the Table

CREATE TABLE geeksforgeeks (
    FIRSTNAME VARCHAR(20),
    LASTNAME VARCHAR(20),
    CITY VARCHAR(20),
    AGE INT,
    GENDER VARCHAR(20)
);

Step 2: Insert Sample Data

INSERT INTO geeksforgeeks VALUES ('ROMY', 'Kumari', 'New Delhi', 22, 'female');
INSERT INTO geeksforgeeks VALUES ('Pushkar', 'jha', 'New Delhi', 23, 'male');
INSERT INTO geeksforgeeks VALUES ('Sujata', 'jha', 'Bihar', 30, 'female');
INSERT INTO geeksforgeeks VALUES ('Roshini', 'Kumari', 'Bihar', 16, 'female');
INSERT INTO geeksforgeeks VALUES ('Avinav', 'Pandey', 'New Delhi', 21, 'male');

Step 3: View the Data

SELECT * FROM geeksforgeeks;

Output

Example 1: Remove the Last Character from the FIRSTNAME Column

Now to delete the last N characters from the field we will use the geeksforgeeks table. To remove the last character from the FIRSTNAME column, we can use the SUBSTRING() and LEN() functions as follows. Below is the query for the SUBSTRING() function to delete the last characters from the field

Query:

SELECT SUBSTRING(FIRSTNAME, 1, LEN(FIRSTNAME) - 1) AS FIRSTNAME, LASTNAME, AGE FROM demo_table;

Output

Explanation:

• The LEN(FIRSTNAME) - 1 part of the query calculates the length of the FIRSTNAME column and subtracts 1 from it.
  
• This ensures that only the first part of the string (up to the second-to-last character) is retrieved, effectively removing the last character from each entry.

Example 2: Remove the Last N Characters from the FIRSTNAME Column

If we need to remove more than just the last character, we can modify the query to remove the last N characters from the string. For instance, if we want to remove the last 3 characters from the FIRSTNAME column, the query would be:

Query:

SELECT SUBSTRING(FIRSTNAME, 1, LEN(FIRSTNAME) - 3) AS FIRSTNAME, LASTNAME, AGE FROM demo_table;

Output

Explanation:

• Here, the query removes the last 3 characters from the FIRSTNAME column by subtracting 3 from the total length of the string.
  
• The result is a substring that contains the first part of each name, excluding the last 3 characters.

Example3: Delete the Last 2 Characters from the FIRSTNAME Column

If we want to remove just the last 2 characters from the FIRSTNAME column, we can use the following SQL query. This query will remove exactly the last two characters from each value in the FIRSTNAME column. It is particularly useful when dealing with names or data entries that consistently need trimming of specific characters at the end.

Query:

SELECT SUBSTRING(FIRSTNAME, 1, LEN(FIRSTNAME) - 2) FROM geeksforgeeks;

Output

Explanation:

• The LEN(FIRSTNAME) function returns the length of the string in the FIRSTNAME column.
  
• Subtracting 2 from LEN(FIRSTNAME) effectively removes the last two characters of each value in the FIRSTNAME column.
  
• SUBSTRING(FIRSTNAME, 1, LEN(FIRSTNAME) - 2) starts at the first character and retrieves a substring of the length calculated by LEN(FIRSTNAME) - 2, thereby excluding the last two characters.

Conclusion

In this article, we explained how to remove the last N characters from a field in SQL using the SUBSTRING() and LEN() functions. We covered different scenarios, such as removing just the last character or multiple characters, and provided detailed examples with outputs to help us understand the process. By mastering these SQL string functions, we can easily manipulate and clean our data, ensuring consistency and accuracy in our database queries.

romy421kumari

Follow

Improve

Article Tags :

• SQL
• Blogathon
• Databases
• Blogathon-2021
• SQL-Query

+1 More