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

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• Perform search/replace for only the first occurrence of a character with session variable in MySQL
• Replace first occurrence of a character in Java
• How to search for ^ character in a MySQL table?
• MySQL search and replace record from a list of records
• How to replace a character in a MySQL table?
• C program to replace all occurrence of a character in a string
• MongoDB Query to search for records only in a specific hour?
• How to cut only the first character in MySQL string?
• How to replace only the first repeated value in a string in MySQL
• String function to replace nth occurrence of a character in a string JavaScript
• MySQL query to replace only the NULL values from the table?
• Implement MySQL trigger in the first table to insert records in the second table?
• A single MySQL query to insert records (not all) in the second table from the first table
• Search records on the basis of date in MySQL?
• Fastest way to search for a date from the date records in MySQL