Skip to Main Content

 
Titelbild Muniqsoft Training

Auswahl  

Komplett Übersicht aller Oracle Tipps

Regular Expression in Oracle (Working Examples) 

Oracle
SQL
RDBMS 12.x
09.09.23 (MP)
05.04.25(MP)

Passende Schulungen zum Thema

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.