Replace a dynamically created Substring in Oracle query

Question

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

Aleksej · Accepted Answer · 2016-03-24 11:11:43Z

2

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

edited Mar 24, 2016 at 11:11

answered Mar 24, 2016 at 8:17

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Omkar Over a year ago

Thanks @Aleksej, thats Great! worked for me,. Just an add on question for the same. What if same data contains Alphanumeric value also like for e.g. 'Hello Mike, Your registered no. is ABC12534 and optional no. is 3525'. Here length of ABC12534 varies for different data. Can I've a solution for this too.

Lalit Kumar B · Accepted Answer · 2016-03-24 09:13:22Z

2

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>

answered Mar 24, 2016 at 9:13

Lalit Kumar B

49.5k13 gold badges105 silver badges132 bronze badges

3 Comments

J. Chomel Over a year ago

Good to know this "TRANSLATE" function. But it doesn't work now that registered no. can contain alpha chars.

Lalit Kumar B Over a year ago

@J.Chomel It will work, just need to tweak accordingly. Can you show me your desired output?

J. Chomel Over a year ago

The disered output is asked above by Omkar: What if same data contains Alphanumeric value also like for e.g. 'Hello Mike, Your registered no. is ABC12534 and optional no. is 3525'. Here length of ABC12534 varies for different data.

J. Chomel · Accepted Answer · 2016-03-24 08:55:22Z

1

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

answered Mar 24, 2016 at 8:55

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

Collectives™ on Stack Overflow

Replace a dynamically created Substring in Oracle query

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related