Reputation: 759

Replace unknown number of characters in a string with '*' in Oracle

Given a column MEMO_TXT has the following text in one MEMO table:
'Password changed from: 12345 to: abcdefg.'

How can i replace 12345 and abcdefg in the above text with equally sized '*' characters?

like,
'Password changed from: 12345 to: abcdefg.'
'Password changed from: ***** to: *******.'

The from and to parts in the above string can vary and i'm not sure how to write an UPDATE query with REPLACE since i have no fixed pattern to look for.

Upvotes: 0

Answers (3)

Littlefoot

Reputation: 142705

If message text is always in that format, good old SUBSTR + INSTR + REPLACE can do the job.

SQL> WITH test (col)
  2       AS (SELECT '&message' FROM DUAL),
  3       pos
  4       AS (SELECT col,
  5                  INSTR (col, ':', 1, 1)
  6                     colon_1,
  7                  INSTR (col, ':', 1, 2)
  8                     colon_2,
  9                  LENGTH (col) len
 10             FROM test),
 11       inter
 12       AS (SELECT col,
 13                  trim(SUBSTR (col, colon_1 + 2, colon_2 - 4 - colon_1)) old_pw,
 14                  TRIM (RTRIM (SUBSTR (col, colon_2 + 1, len - colon_2), '.'))
 15                     new_pw
 16             FROM pos)
 17  SELECT REPLACE (REPLACE (col, old_pw, LPAD ('*', LENGTH (old_pw), '*')),
 18                  new_pw,
 19                  LPAD ('*', LENGTH (new_pw), '*')) result
 20    FROM inter;
Enter value for message: Password changed from: 12345  to:  abcdefg.

RESULT
-------------------------------------------
Password changed from: *****  to:  *******.

SQL> /
Enter value for message: Password changed from: abxyz44#  to:  87ZU_3.

RESULT
----------------------------------------------
Password changed from: ********  to:  *******

SQL>

Upvotes: 1