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.
SELECT SUBSTRING('HELLO GEEKS', 1, 5);
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.
CREATE DATABASE geeks;
Step 2: Using the Database
Use the below SQL statement to switch the database context to geeks.
Step 3: Table definition
We have the following demo_table in our geek’s database.
CREATE TABLE demo_table (FIRSTNAME VARCHAR(20), LASTNAME VARCHAR(20), AGE INT);
Step 4: Insert data
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
SELECT * FROM demo_table;
Step 6: Use of SUBSTRING() function
- We will remove the last character of entries in the LASTNAME column.
SELECT SUBSTRING(column_name,1,LEN(column_name)-1) FROM table_name;
SELECT FIRSTNAME, SUBSTRING(LASTNAME,1,LEN(LASTNAME)-1) AS LASTNAME, AGE FROM demo_table;
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.
SELECT SUBSTRING(FIRSTNAME,1,LEN(FIRSTNAME)-1) AS FIRSTNAME, LASTNAME, AGE FROM demo_table;