Hello Friends, Today I am going to share sql query to update portion of a string in mysql.
The following is the SQL syntax to update portion of a string.
1 2 3 |
UPDATE table SET field = REPLACE(field, 'string', 'anothervalue') WHERE field LIKE '%string%'; |
Create Table Query
The below create table query will create a table with name user_transactions.
1 2 3 4 5 6 7 8 |
CREATE TABLE `user_transactions` ( `id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT, `user_id` int(11) NOT NULL, `txn_date` datetime NOT NULL, `credit` decimal(10,2) NOT NULL, `debit` decimal(10,2) NOT NULL, `comments` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Insert Query
The below query will add some data to table so that we can run select query on it.
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO `user_transactions` (`id`, `user_id`, `txn_date`, `credit`, `debit`, `comments`) VALUES (1, 2, '2018-07-21 04:26:14', '453.00', '0.00', 'test1'), (2, 7, '2018-08-17 00:00:00', '50.00', '0.00', 'test2'), (4, 7, '2018-08-18 04:34:59', '0.00', '10.00', 'test3'), (5, 7, '2018-08-18 04:38:13', '0.00', '5.00', 'Order Placed'), (6, 7, '2018-08-18 04:59:19', '0.00', '10.00', 'test4'), (7, 7, '2018-08-18 09:02:21', '0.00', '25.00', 'Order Placed '), (8, 9, '2018-09-03 12:43:47', '57.25', '0.00', 'Received credits'), (9, 9, '2018-09-03 12:43:47', '0.00', '3.00', 'Charged Commission'), (10, 9, '2018-09-04 10:46:05', '21.00', '0.00', 'Received credits') |
Select Query before Update
showing table data before update
1 |
SELECT * FROM user_transactions |
Output
Update Query
1 2 3 |
UPDATE user_transactions SET comments = REPLACE(comments, 'Placed', 'Delivered') WHERE comments LIKE '%Placed%'; |
Output
Select Query before Update
showing table data before update
1 |
SELECT * FROM user_transactions |
Output
Thanks for Visiting
Please do share if you find it useful
Comments