SQL Query to Delete Last N Characters From Field
SQL stands for Structured Query Language. It is used to communicate with the database. There are some standard SQL commands like ‘select’, ‘delete’, ‘alter’ etc. To delete the last N characters from the field we will use the string function.
It is used to perform an operation on an input string and return an output string. There are various string functions like LEN(for SQL server), SUBSTR, LTRIM, TRIM, etc.
To perform the required function we need the following functions:
1. SUBSTRING(): This function is used to find a sub-string from the string from the given position. It takes three parameters:
- String: It is a required parameter. It provides information about the string on which function is 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 whole string.
SUBSTRING('geeksforgeeks', 1, 5);
2. LEN(): This syntax is not the standard one. For different server syntax for returning the length of a string may vary. For example, LEN() is in SQL server, LENGTH() is used in oracle database, and so on.
It takes only one parameter that is the string whose length you need to find.
To delete the last N characters from the field we will use the following query:
SUBSTRING(string, 1, length(string)-N)
Here, string denotes the field, 1 denotes the starting position of string, and length(string)-N denotes the length of the string. For the purpose of demonstration, we will be creating a geeksforgeeks table in a database called “geeks“.
Creating the Database:
Use the below SQL statement to create a database called geeks:
CREATE DATABASE geeks;
Using the Database:
Use the below SQL statement to switch the database context to geeks:
Table definition: We have the following geeksforgeeks table in our geek’s database.
CREATE TABLE geeksforgeeks(FIRSTNAME VARCHAR(20),LASTNAME VARCHAR(20),CITY VARCHAR(20), AGE INT,GENDER VARCHAR(20));
Adding data to the TABLE: Use the below statement to add data to the geeksforgeeks table:
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');
To see the content of the table: Use the below command to see the content of the geeksforgeeks table:
SELECT * FROM geeksforgeeks;
Now to delete the last N characters from the field we will use the geeksforgeeks table. Below is the syntax for the SUBSTRING() function to delete the last N characters from the field.
SELECT SUBSTRING(column_name,1,length(column_name)-N) FROM table_name;
Example: Delete the last 2 characters from the FIRSTNAME column from the geeksforgeeks table.
SELECT SUBSTRING(FIRSTNAME,1,len(FIRSTNAME)-2) FROM geeks for geeks;
Delete the last 3 characters from the country column and then view it.
SELECT FIRSTNAME AS BEFORE, SUBSTRING(FIRSTNAME,1,len(FIRSTNAME)-3)AS AFTER FROM geeksforgeeks;