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
Is there anything like substr_replace in MySQL?
For this, use the INSERT() function from MySQL. The INSERT(str, pos, len, newstr) returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string.
It replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.
Let us first create a table −
mysql> create table DemoTable ( Password varchar(50) ); Query OK, 0 rows affected (0.51 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values('76367_____8793443');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('12345_____9899984322');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('99999_____4747747432');
Query OK, 1 row affected (0.22 sec)Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+----------------------+ | Password | +----------------------+ | 76367_____8793443 | | 12345_____9899984322 | | 99999_____4747747432 | +----------------------+ 3 rows in set (0.00 sec)
Following is the query to implement INSERT() function for substr_replace in MySQL −
mysql> select insert(Password,5,length('PPPPPP'),'PPPPPP') from DemoTable;This will produce the following output −
+----------------------------------------------+
| insert(Password,5,length('PPPPPP'),'PPPPPP') |
+----------------------------------------------+
| 7636PPPPPP8793443 |
| 1234PPPPPP9899984322 |
| 9999PPPPPP4747747432 |
+----------------------------------------------+
3 rows in set (0.00 sec)
124 Views
