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)

Advertisements