SQL Statement to Remove Part of a String
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;
CREATE DATABASE geeks;
Step 2: Using the database
Use the below SQL statement to switch the database context to geeks:
Step 3: Table creation
We have the following demo_table in our geek’s database.
CREATE TABLE demo_table( NAME VARCHAR(20), GENDER VARCHAR(20), AGE INT, CITY VARCHAR(20) );
Step 4: Insert data into a table
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
SELECT * FROM demo_table;
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.
SELECT SUBSTRING(column_name,1,length(column_name)-4) FROM table_name;
Remove the last 4 characters from the NAME field.
SELECT SUBSTRING(NAME,1,len(NAME)-4) AS NAME, GENDER, AGE, CITY FROM demo_table;
Method 2 : Using REPLACE() function
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
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:
SELECT NAME, GENDER, AGE, REPLACE(CITY,'New','') AS CITY FROM demo_table;
We are not replacing it with a new string.
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.
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:
SELECT NAME, GENDER, AGE, TRIM ('NEW' FROM CITY)AS "NEW CITY" FROM demo_table;