MySQL string replace – MySQL: Remove characters from string

by

MySQL string replace: In this article we are going to discuss how to remove characters from string in MySQL table.

MySQL: Remove characters from string

Here we are going to discuss three methods to remove characters from a database table in MySQL

Lets first make a database table in MySql,

CREATE TABLE student_data (
student_id INT,
student_name VARCHAR(50),
enroll_date DATE,
student_roll_no BIGINT,
fee_submitted DECIMAL(10,2)
);

Insert values in student_data,

INSERT INTO student__data(student_id,student_name,enroll_date,student_roll_no,fee_submitted) 
VALUES(1,"DShr-tht-ee,",'2020-12-02',1147483782,12378.90),
(2,"SShy-tht-am,",'2020-10-03',1147483788,14578.90),
(3,"RRi-tht-iky,",'2020-11-13',1147483789,22378.90),
(4,"JAb-tht-ir," ,'2020-12-04',1147483790,12378.90),
(5,"AAust-tht-gya,",'2020-11-12',1147483791,12378.90),
(6,"GPi-tht-hu,",'2020-10-10',1147483792,12788.90),
(7,"VPar-tht-nica,",'2020-02-14',1147483793,12378.90);

Output:

Remove chara from string in sql

Remove characters from string using REPLACE()

Here we are going to remove unwanted character from string using REPLACE() function.

Syntax:

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

Explanation:

tableName- Name of the given table

columnName: Column name whose value has to chnage

charactersToBeReplaced:Characters which we want to replaced

charactersToBeReplacedWith:New characters which we want to put instead of replaced one

Now we are going to remove “tht” from our string in column “student_name”,

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

In above code you can see that we have given “-tht-” in place of characterToBeReplaced,which replaced the character and give us below output.

Output:

Remove char from string2

Remove characters from string using TRIM()

Here we are going to remove unwanted character from string using TRIM() function.TRIM() function are very helpful in removing char from string.It deletes special characters  or any characters given in start or end of table,

Syntax:

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

Explanation:

tableName- Name of the given table

columnName: Column name whose value has to chnage

charactersToBeRemoved:Characters which we want to replaced

BOTH,:When we want to remove char from start and end.

LEADING: Remove character from starting.

TRAILING: Remove characters from the end.

Now we are going to remove character ‘,’  from end for this will use below query;

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

Output:

Remove char from string3png

So you can see that all “,” removed from end in Table student__data.

Remove characters from string using SUBSTRING()

Here we are going to remove unwanted character from string using SUBSTRING() function.It will remove substring from table in MySql.

Syntax:

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

Explanation:

tableName- Name of the given table

columnName: Column name whose value has to chnage

pos:position from where the substring will start.

Here I am going to remove first character of column “student_name”,

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

Output:

Remove char from string4
Conclusion:

In this article we have discussed how to remove characters from string in MySQL table.Thank You!