Perform search/replace for only the first occurrence of a character in MySQL table records?

You can achieve this with the help of CONCAT() along with REPLACE() function. To find the first occurrences you need to use INSTR() function.

The syntax is as follows −

UPDATE yourTableName
SET UserPost = CONCAT(REPLACE(LEFT(yourColumnName, INSTR(yourColumnName, 'k')), 'k', 'i'),
SUBSTRING(yourColumnName, INSTR(yourColumnName, 'k') + 1));

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table UserInformation
   -> (
   -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> UserName varchar(10),
   -> UserPost text
   -> );
Query OK, 0 rows affected (2.05 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into UserInformation(UserName,UserPost) values('Larry','Thks is a MySQL query');
Query OK, 1 row affected (0.16 sec)
mysql> insert into UserInformation(UserName,UserPost) values('Mike','Thks is not a java program');
Query OK, 1 row affected (0.31 sec)
mysql> insert into UserInformation(UserName,UserPost) values('Sam','Thks is a SQL syntax');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from UserInformation;

The following is the output −

+--------+----------+----------------------------+
| UserId | UserName | UserPost                   |
+--------+----------+----------------------------+
|      1 | Larry    | Thks is a MySQL query      |
|      2 | Mike     | Thks is not a java program |
|      3 | Sam      | Thks is a SQL syntax       |
+--------+----------+----------------------------+
3 rows in set (0.00 sec)

Here is the query to search/replace but only the first time a value appears in a record. Here, the first occurrence of ‘k’ is replaced with ‘i’ −

mysql> update UserInformation
   -> set UserPost=CONCAT(REPLACE(LEFT(UserPost, INSTR(UserPost, 'k')), 'k', 'i'),
   -> SUBSTRING(UserPost, INSTR(UserPost, 'k') + 1));
Query OK, 3 rows affected (0.16 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Display all records from the table once again. The query is as follows −

mysql> select *from UserInformation;

The following is the output displaying the first occurrence of ‘k’ replaced with ‘I’ −

+--------+----------+----------------------------+
| UserId | UserName | UserPost                   |
+--------+----------+----------------------------+
|      1 | Larry    | This is a MySQL query      |
|      2 | Mike     | This is not a java program |
|      3 | Sam      | This is a SQL syntax       |
+--------+----------+----------------------------+
3 rows in set (0.00 sec)

Samual Sam

Published on 27-Feb-2019 07:50:27

Previous Page Print Page