CODEWITHSUNDEEP
sqloracle-databaseplsql
oradbanj
oradbanj

Reputation: 563

Oracle : removing certain characters from string

We have to remove certain characters from VARCHAR2 and CLOB data types using SQL or PL/SQL and load into destination tables.

There are functions available in Oracle ( e.g REGEXP_REPLACE or SUBSTR ) which can be used.

However, we have large amount of data.

Will it be faster if we extract the data into Linux host and use a combination of TR ( /bin/tr ) and Oracle External Files ?

Upvotes: 1

Views: 10232

Answers (2)

Maicon Santos
Maicon Santos

Reputation: 41

You can also use REPLACE, see the examples:

select REPLACE('AB 123456','AB','YZ') from dual;

It returns: 'YZ 123456'

In the next example, the third parameter of the REPLACE function has been omitted, so the corresponding string is removed from the original one.

select REPLACE('AB 123456','AB ') from dual;

It returns: '123456'

See full documentation on Oracle SQL Language Reference - REPLACE

Upvotes: 0

D. Mika
D. Mika

Reputation: 2828

I usually use TRANSLATE (see: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRANSLATE.html#GUID-80F85ACB-092C-4CC7-91F6-B3A585E3A690) to delete characters from a string. But it depends a bit on how many characters you want to delete.

The following example is intended to illustrate this. The characters 'D' and 'E' are deleted from the input string.

SELECT TRANSLATE('ABCDEFG', '_DE', '_') FROM DUAL;

It returns 'ABCFG'.

'ABCDEFG' is the input string. '_DE' means 'D' and 'E' are removed.

Upvotes: 3

Related Questions