CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Omkar
Omkar

Reputation: 303

Replace a dynamically created Substring in Oracle query

I'm using Oracle 11g database which contains data where in I want to replace a dynamically generated text with a dummy text using a oracle query. For e.g my column in table contain data : Hello Mike, Your registered no. is 3525. Kindly check the same .

Now the issue is, Name of customer i.e. 'Mike' can be dynamic that is why I'm not able to use SUBSTR function. And I want to replace 3525 with XXXX E.g Hello Mike, Your registered no. is XXXX. Kindly check the same.

Please help me with the issue. I'm using Oracle 11g

Upvotes: 0

Views: 1231

Answers (3)

Aleksej
Aleksej

Reputation: 22949

If the only dynamic part of you string is the name ( and assuming that names do not contain numbers...) you can try:

select regexp_replace('Hello Mike, Your registered no. is 3525. Kindly check the same',
                      '([0-9])',
                      'X'
                     )
from dual

This simply replaces every numeric character with 'X'.

To replace things like, for example, 'AB3525', with a fixed string, say 'XXXX', you can try replacing it with a fixed :

select regexp_replace('Hello Mike, Your registered no. is AB3525. Kindly check the same',
                      '(Hello [^\,]*\, Your registered no. is )([^\.]*)(\. Kindly check the same)',
                      '\1XXXX\3'
                     )            
from dual

Upvotes: 2

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You could use TRANSLATE which would be much faster than REGULAR EXPRESSION. It would simply any occurrence of a number with X.

For example,

SQL> SELECT TRANSLATE('Hello Mike, Your registered no. is 3525. Kindly check the same',
  2                   '0123456789',
  3                   'XXXXXXXXXX') str
  4  FROM dual;

STR
--------------------------------------------------------------
Hello Mike, Your registered no. is XXXX. Kindly check the same

SQL>

Upvotes: 2

J. Chomel
J. Chomel

Reputation: 8395

Here is another way: you can split your string in several expression, and access the matches with \{position number}

select regexp_replace('Hello Mike, Your registered no. is 3525. Kindly check the same',
                      '(Hello )(.*)(, Your registered no. is )(.*)(. Kindly check the same)',
                      'Hello \2, Your registered no. is [xxx]. Kindly check the same'
                     )
from dual

Upvotes: 1

Related Questions