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
- Remove Characters From String using REPLACE()
- Remove Characters From String using TRIM()
- Remove Characters From String using SUBSTRING()
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 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 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:
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:
Conclusion:
In this article we have discussed how to remove characters from string in MySQL table.Thank You!