In today's tip, we will look at a few practical examples of regular expressions in Oracle.
Example 1: Credit card numbers usually have the format: 1234-1234-1234-1234
We look for two blocks of 4 in a string with numbers and a minus in between and replace each block with: xxxx-xxxx:
- [[:digit:]] stands for numbers only
- [4} 4 Numbers
select regexp_replace(
'1234-5678-1111-2222',
'[[:digit:]]{4}-[[:digit:]]{4}','xxxx-xxxx') from dual;
Result: xxxx-xxxx-xxxx-xxxx
Or we look for four blocks of 4 with numbers and replace this with: XXXX:
select regexp_replace(
'1234-5678-1111-2222',
'[[:digit:]]{4}','xxxx')
from dual;
Result: xxxx-xxxx-xxxx-xxxx
Only the last block of 4 is to be issued:
select regexp_replace(
'1234-5678-1111-2222',
'([[:digit:]]{4})(-)([[:digit:]]{4})(-)([[:digit:]]{4})(-)([[:digit:]]{4})','XXXX-XXXX-XXXX-\7') from dual;
Result:
XXXX-XXXX-XXXX-2222
Alternatively, instead of [[:digit:]] with \d replace
select regexp_replace(
'1234-5678-1111-2222',
'\d{4}','x')
from dual;
Result: x-x-x-x
Beispiel 2: Wir löschen einige HTML Tags in einem String:
- \/?div finds div,
- \/?p finds p and
- \/?strong findsstrong Tags
- (/ is a special Character and have to be \ escaped)
select regexp_replace(
'<div>Hallo</div><strong> Kurs</strong>',
'(<\/?div>|<\/?p>|<\/?strong>)','')
from dual;
or we delete all HTML tags in the string
select regexp_replace(
'<div>Hallo</div><strong> Training</strong>',
'<\/?[^>]*>,'')
from dual;
This was a small selection of examples, which we are constantly expanding. Or you can come to the PL/SQL or PL/SQL II course, for example, where these topics are also discussed in detail.