Oracle SQL - REGEXP_REPLACE to replace all words

  1. HOME
  2. Oracle SQL
  3. Select
  4. Regular Expressions

Introduction

The following code shows how you can use the REGEXP_REPLACE function to replace all words starting with an f with a question mark.

You must specify values for all function arguments if you want to make the replacement case-insensitive, including default values for pos and occurrence.

The WHERE clause ensures that the query returns only the matching rows.

Demo

SQL>
SQL> drop table history;

Table dropped.--  w  w  w .  j av  a 2 s . c  o m

SQL> create table history(
  2  comments   VARCHAR2(60)) ;
SQL>
SQL> insert into history values ('this is a test');
SQL> insert into history values ('salary reduction this is a test');
SQL> insert into history values ('this is a test this is a test Transfer to sales department');
SQL> insert into history values ('Not a great trainer; this is a test this is a test this is a test!');
SQL> insert into history values ('Senior sales this is a test this is a test this is a test?');
SQL> insert into history values ('Turns out to be slightly disappointing this is a test this is a test');
SQL> insert into history values ('Transfer to human resources; salary raise this is a test');
SQL> insert into history values ('Salary reduction 550 this is a test this is a test');
SQL> insert into history values ('Founder and first employee of the company');
SQL> insert into history values ('Accounting established this is a test this is a test');
SQL> insert into history values ('Project for the ACCOUNTING department this is a test');
SQL>
SQL> select regexp_replace(comments, ' f[a-z]* ',' ? ',1,1,'i')
  2  from   history
  3  where  regexp_like(comments, ' f[a-z]* ','i');

REGEXP_REPLACE(COMMENTS,'F[A-Z]*','?',1,1,'I')
--------------------------------------------------------------------------------
Founder and ? employee of the company
Project ? the ACCOUNTING department this is a test

SQL>

Related Topic