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

Here we will see, how to remove the last characters from a table in SQL. We can do this task using the SUBSTRING() function.

SUBSTRING(): This function is used to find a part of the given string from the given position. It takes three parameters:  

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

Query:

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

Output:

To delete the last character from the field, pass the length parameter to be 1 less than the total length.

For the purpose of demonstration let’s create a demo_table in a database named ‘geeks’.

Step 1: Creating the Database

Use the below SQL statement to create a database called geeks.

Query:

CREATE DATABASE geeks;

Step 2: Using the Database

Use the below SQL statement to switch the database context to geeks.

Query:

USE geeks;

Step 3: Table definition

We have the following demo_table in our geek’s database.

Query:

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

Step 4: Insert data

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 5: View the content

Query:

SELECT * FROM demo_table;

Output:

Step 6: Use of SUBSTRING() function

• We will remove the last character of entries in 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:

We can see in the image that the last character from the LASTNAME column is removed now.

• We will remove the last character of entries in the FIRSTNAME column.

Query:

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

Output:

My Personal Notes arrow_drop_up

Save