
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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)
- Related Articles
- 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?
- String function to replace nth occurrence of a character in a string JavaScript
- How to cut only the first character in MySQL string?
- How to replace only the first repeated value in a string in MySQL
- Implement MySQL trigger in the first table to insert records in the second table?
- MySQL query to replace only the NULL values from the table?
- A single MySQL query to insert records (not all) in the second table from the first table
- Fastest way to search for a date from the date records in MySQL
- How to get everything before the last occurrence of a character in MySQL?
