Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
2K+ Views
