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.

Query:

SUBSTRING('w3wiki', 1, 5);

Output:

Beginner

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.

Query:

LEN('w3wiki')

Output:

13

To delete the last N characters from the field we will use the following query:

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 w3wiki table in a database called “Beginner“.

Creating the Database:

Use the below SQL statement to create a database called Beginner:

CREATE DATABASE Beginner;

Using the Database:

Use the below SQL statement to switch the database context to Beginner:

USE Beginner;

Table definition: We have the following w3wiki table in our geek’s database.

Query:

  CREATE TABLE w3wiki(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 w3wiki table:

Query:

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

To see the content of the table: Use the below command to see the content of the w3wiki table:

Query:

SELECT * FROM w3wiki;

Now to delete the last N characters from the field we will use the w3wiki table. Below is the syntax for the SUBSTRING() function to delete the last N characters from the field.

Syntax:

SELECT SUBSTRING(column_name,1,length(column_name)-N) FROM table_name;

Example: Delete the last 2 characters from the FIRSTNAME column from the w3wiki table.

Query: 

SELECT SUBSTRING(FIRSTNAME,1,len(FIRSTNAME)-2) FROM Beginner for Beginner;

Output:

Delete the last 3 characters from the country column and then view it.

Query:

SELECT FIRSTNAME AS BEFORE, SUBSTRING(FIRSTNAME,1,len(FIRSTNAME)-3)AS AFTER FROM w3wiki;

Output: