sql query to replace

Question

I have a string in my description field in MySQL like this :

"text text text pass:123456789 text text text"

I want to replace 4 characters after "pass:" to star. result:

"text text text pass:****56789 text text text"

I use this

UPDATE table_name SET field = REPLACE(description , '123456789', '****56789') WHERE field LIKE '%123456789%';

if password change my query dont work.

what do you mean with "if password change my query dont work." .?? . try explain better .. add a proper data sample — ScaisEdge
– ScaisEdge, Commented Apr 25, 2020 at 6:49
for example: "text text text pass:987654321 text text text" cant find 987654321 and replace with ****54321 — Benyamin Karimi
– Benyamin Karimi, Commented Apr 25, 2020 at 6:51

Manuel Zelenka · Accepted Answer · 2020-04-25 08:29:32Z

As already mentioned in my comment you could do this with REGEXP_REPLACE, assuming your comment that this post is regarding MariaDB instead of mysql is more correct.

So given your example I'd write the following statement:

UPDATE table_name SET field = REGEXP_REPLACE(description , '\bpass:\w{4}', 'pass:****') WHERE field LIKE '%123456789%';

If you're not familiar with regular expression what this does is basically:

search a word that begins with "pass:" and then has four additional characters after it in the boundary of a word.
replace the found strings with "pass:****"

A word in regular expressions is btw. something that contains alphanumeric and underscore characters. Valid would be for example "This_is_1_word", "Word", "123153".

So my example will only work with passwords that only contain the mentioned alphanumeric and underscore characters.

To fit every need you'd need to specify which characters are allowed for the password. I could adapt the given regular expression based on your specs

Collectives™ on Stack Overflow

sql query to replace

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related