
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How can we update MySQL table after removing a particular string from the values of column?
We can update MySQL table after removing a particular string from the values of a column by using TRIM() function along with UPDATE clause. Following the example from ‘examination_btech’ table will make it clearer −
Example
Suppose if we want to delete the values ‘(CSE)’, from last, of column ‘Course’ and want to update the table too then it can be done with the help of the following query −
mysql> Update examination_btech SET Course = TRIM(Trailing '(CSE)' FROM Course); Query OK, 10 rows affected (0.13 sec) mysql> Select * from examination_btech; +-----------+----------+--------+ | RollNo | Name | Course | +-----------+----------+--------+ | 201712001 | Rahul | B.tech | | 201712002 | Raman | B.tech | | 201712003 | Sahil | B.tech | | 201712004 | Shalini | B.tech | | 201712005 | Pankaj | B.tech | | 201712006 | Mohan | B.tech | | 201712007 | Yash | B.tech | | 201712008 | digvijay | B.tech | | 201712009 | Gurdas | B.tech | | 201712010 | Preeti | B.tech | +-----------+----------+--------+ 10 rows in set (0.00 sec)
From the above result set, it is clear that ‘(CSE)’ has been removed from last of column ‘course’ and the table also got updated.
Similarly, with the help of TRIM() function, we can remove the string from starting and update the table.
mysql> Update examination_btech SET RollNo = TRIM(Leading '201712' FROM RollNo); Query OK, 10 rows affected (0.04 sec) mysql> Select * from examination_btech; +--------+----------+--------+ | RollNo | Name | Course | +--------+----------+--------+ | 1 | Rahul | B.Tech | | 2 | Raman | B.Tech | | 3 | Sahil | B.Tech | | 4 | Shalini | B.Tech | | 5 | Pankaj | B.Tech | | 6 | Mohan | B.Tech | | 7 | Yash | B.Tech | | 8 | digvijay | B.Tech | | 9 | Gurdas | B.Tech | | 10 | Preeti | B.Tech | +--------+----------+--------+ 10 rows in set (0.00 sec)
From the above result set, it is clear that ‘201712’ has been removed from starting of column ‘RollNo’ and the table also got updated.
- Related Articles
- How can we update MySQL table after padding a string with the values of the column?
- How can we update values in a MySQL table?
- How can I update MySQL table after quoting the values of a column with a single quote?
- How can we update any value in MySQL view as we can update the values in MySQL table?
- How can we update the values in one MySQL table by using the values of another MySQL table?
- How can we remove a column from MySQL table?
- How can we fetch all the records from a particular MySQL table?
- How can we fetch a particular row as output from a MySQL table?
- How to update a MySQL table by swapping two column values?
- How can we count a number of unique values in a column in MySQL table?
- How can we extract a substring from the value of a column in MySQL table?
- Can we add a column to a table from another table in MySQL?
- How can we modify column/s of MySQL table?
- How can we match the values having backslashes, like ‘a\b’, from MySQL column?
- How can we use LPAD() or RPAD() functions with the values in the column of a MySQL table?
