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

Table of Contents

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

image_1: student_enroll_data

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');
NameDescription
tableNameName of the table.
columnNameName of the column whose values are to be updated.
charactersToBeReplacedThe characters to be removed from each value.
charactersToBeReplacedWithThe new characters which will be part of each column value instead of charactersToBeReplaced.
table_1

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

image_2

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);
NameDescription
tableNameName of the table.
columnNameName of the column whose values are to be updated.
charactersToBeRemovedThe characters to be removed from each value.
BOTH, LEADING,TRAILING BOTH: used when we want to remove characters from beginning and end.
LEADING: used when we want to remove characters from the beginning.
TRAILING: used when we want to remove characters from the end.
BOTH/LEADING/TRAILING is optional.
** If none of the parameters are present, BOTH is applied by default.
table_2

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

image_3

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);
NameDescription
tableNameName of the table.
columnNameName of the column whose values are to be updated.
posPosition from where the substring will start.
table_3

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

image_4

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!!!
You need to add a widget, row, or prebuilt layout before you’ll see anything here. 🙂