- Trending Categories
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
How do I search and replace specific chars at the beginning of a string in MySQL?
For this, you can use INSERT(). Let us first create a table −
mysql> create table DemoTable -> ( -> ZipCode varchar(200) -> ); Query OK, 0 rows affected (0.47 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values('9030');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('3902');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('9083');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('9089');
Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −
mysql> select *from DemoTable;
Output
+---------+ | ZipCode | +---------+ | 9030 | | 3902 | | 9083 | | 9089 | +---------+ 4 rows in set (0.00 sec)
Following is the query to search and replace chars at the beginning of a string. Here, we are working only on the records with ZipCode beginning from 90 −
mysql> update DemoTable set ZipCode=INSERT(ZipCode, 1, 2, 'Country-AUS-') -> where ZipCode LIKE '90%'; Query OK, 3 rows affected (0.26 sec) Rows matched: 3 Changed: 3 Warnings: 0
Let us check table records once again −
mysql> select *from DemoTable;
Output
+----------------+ | ZipCode | +----------------+ | Country-AUS-30 | | 3902 | | Country-AUS-83 | | Country-AUS-89 | +----------------+ 4 rows in set (0.00 sec)
Updated on: 30-Jul-2019
197 Views
Advertisements