SQL Query to Update Portion of a String Uncategorized by Rajesh Kumar Sahanee - September 27, 2019September 27, 20190 Post Views: 4,615 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. query-1 MySQL UPDATE table SET field = REPLACE(field, 'string', 'anothervalue') WHERE field LIKE '%string%'; 123 UPDATE tableSET 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. Create Table Query MySQL 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; 12345678 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. Insert Query MySQL 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') 12345678910 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 Select Query Before MySQL SELECT * FROM user_transactions 1 SELECT * FROM user_transactions Output Update Query Update Query MySQL UPDATE user_transactions SET comments = REPLACE(comments, 'Placed', 'Delivered') WHERE comments LIKE '%Placed%'; 123 UPDATE user_transactions SET comments = REPLACE(comments, 'Placed', 'Delivered') WHERE comments LIKE '%Placed%'; Output Select Query before Update showing table data before update Select Query Before MySQL SELECT * FROM user_transactions 1 SELECT * FROM user_transactions Output Thanks for Visiting Please do share if you find it useful
