MySQL: Remove characters from string

This article will see how to remove characters from a column value of string datatype in a MySQL table.

Table of Contents

• Remove characters from string using REPLACE()
• Remove characters from string using TRIM()
• Remove characters from string using SUBSTRING()

Let us get started by making the sample table student_enroll_data and inserting a few rows into it.

#create the table 
CREATE TABLE student_enroll_data (
student_id INT,
student_name VARCHAR(50),
enroll_date DATE,
student_ssn_no BIGINT,
fee_submitted DECIMAL(10,2)
);
# inserts rows into table student_enroll_data
INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) 
VALUES(1,"DDan-gyg-iel,",'2021-12-12',1147483782,12378.90),
(2,"SSan-gyg-dy,",'2021-10-12',1147483788,14578.90),
(3,"RRym-gyg-a,",'2021-11-22',1147483789,22378.90),
(4,"JJenn-gyg-ifer," ,'2021-12-02',1147483790,12378.90),
(5,"AAust-gyg-in,",'2021-11-12',1147483791,12378.90),
(6,"GGeo-gyg-rge,",'2021-10-10',1147483792,12788.90),
(7,"VVer-gyg-onica,",'2021-02-13',1147483793,12378.90);

Let us look into what got added to the table student_enroll_data by executing:

SELECT * FROM student_enroll_data;

Output:-

Remove characters from string using REPLACE()

This section will remove a few characters from the string using REPLACE() function of MySQL.

Syntax:-

UPDATE tableName SET columnName = REPLACE(columnName, 'charactersToBeReplaced', 'charactersToBeReplacedWith');

Requirement:- Remove the characters ‘-gyg-‘ from the all the values in the column student_name.

Observe the below query to see the usage of the replace() function.

UPDATE student_enroll_data SET student_name = REPLACE(student_name, '-gyg-', '');

Action Output Message:-

15:51:57 UPDATE student_enroll_data SET student_name = REPLACE(student_name, ‘-gyg-‘, ”) 7 row(s) affected Rows matched: 7 Changed: 7 Warnings: 0 0.0012 sec

Let us now see how the column values are changed by executing:

SELECT * FROM student_enroll_data;

Output:-

Note that in image_2, from the column’s values, student_name characters ‘-gyg-‘ are removed successfully.

Remove characters from string using TRIM()

This section will remove the characters from the string using the TRIM() function of MySQL. TRIM() function is used to remove any character/ whitespace from the start/ end or both from a string.

Let us move ahead by looking into its syntax and application.

Syntax:-

UPDATE tableName SET columnName = TRIM([{BOTH | LEADING | TRAILING} [charactersToBeRemoved] FROM ] columnName);

Requirement:- Remove the character ‘,’ from the end of all the values in the column student_name.

Observe the below query to see the usage of the trim() function.

UPDATE student_enroll_data SET student_name = TRIM(TRAILING ',' FROM student_name);

Action Output Message:-

16:22:30 UPDATE student_enroll_data SET student_name = TRIM(TRAILING ‘,’ FROM student_name) 7 row(s) affected Rows matched: 7 Changed: 7 Warnings: 0 0.0016 sec

Let us now see how the column values are changed by executing:

SELECT * FROM student_enroll_data;

Output:-

Output in image_3 shows that the trailing ‘,’ is removed.

Remove characters from string using SUBSTRING()

This section will remove the characters from the string using the SUBSTRING() function of MySQL. The SUBSTRING() function is used when we expect a string in the output, which is part of the original string.

Let us forge ahead by looking into its syntax and application.

Syntax:-

UPDATE tableName SET columnName = SUBSTRING(columnName,pos);

Requirement:- Remove the first character from all the values in the column student_name.

Observe the below query to see the usage of the substring() function.

UPDATE student_enroll_data SET student_name = SUBSTRING(student_name,2);

Action Output Message:-

16:49:35 UPDATE student_enroll_data SET student_name = SUBSTRING(student_name,2) 7 row(s) affected Rows matched: 7 Changed: 7 Warnings: 0 0.0019 sec

The substring, which will be the new value of the column, will be started from index position 2. Let us now see how the column values are changed by executing:

SELECT * FROM student_enroll_data;

Output:-

Output in image_4 shows that the first character is removed from each value of the column student_name.

We hope this article helped you with removing the non-required characters from the string in MySQL. Good Luck!!!