CODEWITHSUNDEEP
mysqlfunction
BumbleBee
BumbleBee

Reputation: 41

is there anything like substr_replace in MySQL?

I have column data that looks like this "132154646878" And i would like replace a part of each one from a specified position something like :

substr_replace("132154646878","***",4)

Output => 132***646878

Any functions in MySQL?

Upvotes: 2

Views: 581

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

Looks like you are looking for MySQL's INSERT function. 

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. 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.

source https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_insert

Query

SELECT INSERT("132154646878", 4, LENGTH('***'), "***");

p.s keep in mind that when you use multibyte characters charset like utf8 in the newstr parameter you need to use CHAR_LENGTH() instead off LENGTH()

Result

| INSERT("132154646878", 4, LENGTH('***'), "***") |
| ----------------------------------------------- |
| 132***646878                                    |

View on DB Fiddle

Upvotes: 6

Related Questions