Open In App

SQL Query to Delete Last N Characters From Field

Improve
Improve
Like Article
Like
Save
Share
Report

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.

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('geeksforgeeks', 1, 5);

Output:

geeks

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('geeksforgeeks')

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 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:

USE geeks;

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

Query:

  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:

Query:

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:

Query:

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.

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 geeksforgeeks table.

Query

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

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 geeksforgeeks;

Output: 


Last Updated : 27 Sep, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads