CODEWITHSUNDEEP
mysqlsql
Benyamin Karimi
Benyamin Karimi

Reputation: 163

sql query to replace

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.

Upvotes: 0

Views: 43

Answers (1)

Manuel Zelenka
Manuel Zelenka

Reputation: 1634

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

Upvotes: 1

Related Questions