How to Remove the Last Character From a Table in SQL?

SQL (Structured Query Language) allows for efficient data manipulation and retrieval. A common task in SQL involves removing the last character from a specific column within a table. This can be achieved using string functions like SUBSTRING() and LEN(). In this article, we will demonstrate how to achieve this, focusing on a practical example with detailed explanations.

Why Remove the Last Character in SQL?

Removing the last character from a column in SQL is particularly useful in scenarios where data cleanup or formatting is required. For instance, there may be a trailing character, such as a comma or space, that needs to be removed to ensure data consistency. Additionally, we might need to trim specific characters to match a standard format. This operation is also valuable when we need to extract a portion of a string while excluding any unwanted characters, making it easier to maintain clean and usable datasets.

Key SQL String Functions Used

In SQL, string functions are essential for performing various operations on text or string data. Here are some key SQL string functions used in the article, explained in detail:

1. SUBSTRING()

The SUBSTRING() function allows us to extract a specific portion of a string by specifying the starting position and, optionally, the length of the substring. It is commonly used for tasks like isolating a section of a string, such as a name or code, within larger datasets

Syntax

SUBSTRING(string, start_position, length)

Key Terms

• String: It is a required parameter. It is the string on which function is to be 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 substring to be returned.

Example

SELECT SUBSTRING('HELLO GEEKS', 1, 5);

Output:

SQLQue

2. LEN():

The LEN() function is used to determine the number of characters in a string, excluding any trailing spaces. It is particularly useful when we need to work with the exact size of a string, such as when trimming unwanted characters or determining the length of data entries

Syntax

LEN(string)

Example

SELECT LEN('SQLQuery');

Output

8

Example of Removing the Last Character in SQL

In this example, we'll demonstrate how to remove the last character from a string in a column using the SUBSTRING() and LEN() functions.

Step 1: Creating the Table

For the purpose of this demonstration, let's create a table called demo_table in a database named geeks. We will include columns for FIRSTNAME, LASTNAME, and AGE.

Query

 CREATE TABLE demo_table
(FIRSTNAME VARCHAR(20),
LASTNAME VARCHAR(20),
AGE INT);

Step 2: Inserting Data into the Table

Next, we’ll insert some sample data into the demo_table to demonstrate the removal of the last character from the string

Query

INSERT INTO demo_table VALUES
('Romy', 'Kumari', 22 ),
('Pushkar', 'Jha', 23),  
('Meenakshi', 'Jha', 20),
('Shalini', 'Jha', 22),
('Nikhil', 'Kalra', 23),
('Akanksha', 'Gupta', 23);

Step 3: Viewing the Data in the Table

Use the following query to display the content of demo_table:

Query

SELECT * FROM demo_table;

Output

Example 1: Remove the Last Character from LastName

Now that the table is populated with data, we will use the SUBSTRING() function along with LEN() to remove the last character from the LASTNAME column.

Syntax

SELECT SUBSTRING(column_name,1,LEN(column_name)-1) 
FROM table_name;

Query:

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

Output

Explanation:

• SUBSTRING(LASTNAME, 1, LEN(LASTNAME) - 1): This expression extracts the string starting from the first character and excluding the last one (by subtracting 1 from the length of the string).
  
• The result will return the FIRSTNAME, the modified LASTNAME without the last character, and the AGE of each person in the table.

Example 2: Remove the Last Character from FirstName

In this example, we will demonstrate how to remove the last character from the entries in the FIRSTNAME column using the SUBSTRING() and LEN() functions.

Query:

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

Output

Explanation:

• SUBSTRING(FIRSTNAME, 1, LEN(FIRSTNAME) - 1): This function extracts the string starting from the first character of FIRSTNAME and removes the last character by subtracting 1 from the length of the string.
  
• The result will show the modified FIRSTNAME with the last character removed, alongside the LASTNAME and AGE from the table.

Conclusion

String manipulation in SQL is an essential skill for cleaning and formatting data effectively. Removing the last character from a column is a common task that can be efficiently handled using functions like SUBSTRING() and LEN() (or LENGTH() in some databases). These functions allow precise control over string length and content, making it easier to maintain clean and well-structured datasets.

By understanding and applying these techniques, database administrators and developers can enhance data accuracy and streamline operations, ensuring that their databases meet high-quality standards. Mastering such operations not only simplifies routine tasks but also optimizes database performance in the long run.