SQL Statement to Remove Part of a String - GeeksforGeeks (2024)

Here we will see SQL statements to remove part of the string.

Method 1: Using SUBSTRING() and LEN() function

We will use this method if we want to remove a part of the string whose position is known to us.

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.

2. LEN(): The 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.

Let see these above mention function with an example. Suppose to remove unwanted parts of the string we will extract only the wanted part from string characters from the field, we will use the following query:

Step 1: Create a database

Use the below SQL statement to create 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 creation

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

Query:

CREATE TABLE demo_table(NAME VARCHAR(20),GENDER VARCHAR(20),AGE INT,CITY VARCHAR(20) );

Step 4: Insert data into a table

Query:

INSERT INTO demo_table VALUES('ROMY KUMARI', 'FEMALE', 22, 'NEW DELHI'),('PUSHKAR JHA', 'MALE',23, 'NEW DELHI'),('RINKLE ARORA', 'FEMALE',23, 'PUNJAB'),('AKASH GUPTA', 'MALE', 23, 'UTTAR PRADESH');

Step 5: View data of the table

Query:

SELECT * FROM demo_table;

Output:

Step 6: Remove part of a string

Suppose if we want to remove the last 4 characters from the string then, we will extract the remaining part using the below statement.

Syntax:

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

Example :

Remove the last 4 characters from the NAME field.

Query:

SELECT SUBSTRING(NAME,1,len(NAME)-4) AS NAME, GENDER, AGE, CITY FROM demo_table;

Output:

Method 2 : Using REPLACE() function

We can remove part of the string using REPLACE() function. We can use this function if we know the exact character of the string to remove.

REMOVE(): This function replaces all occurrences of a substring within a new substring. It takes three parameters, all are required parameters.

• string Required. The original string
• old_string Required. The string to be replaced
• new_string Required. The new replacement string

Syntax:

REPLACE(string, old_string, new_string)

We will use the above demo_table for the demonstration. Suppose if we remove ‘New’ from the CITY field in demo_table then query will be:

Query:

SELECT NAME, GENDER, AGE, REPLACE(CITY,'New','') AS CITY FROM demo_table;

We are not replacing it with a new string.

Output:

Method 3: Using TRIM() function

TRIM(): This function removes the space character or other specified characters from the start or end of a string. By using this function we can not remove part of the string from the middle of the string.

Syntax:

TRIM([characters FROM ]string);

We will use the above demo_table for the demonstration. Suppose if we want to remove ‘New’ from the CITY field in demo_table then the query will be as follows:

Query:

SELECT NAME, GENDER, AGE, TRIM ('NEW' FROM CITY)AS "NEW CITY" FROM demo_table;

Output: