Oracle : removing certain characters from string

Question

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 ?

There are probably tools out there which can do the replacement faster than Oracle. The question is do you want to go to the trouble of exporting and reimporting? — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 12, 2021 at 12:40
You could benchmark with a subset of the data to know for sure. My guess would be that writing all the data out to disk and reading it all back in would take vastly longer than whatever speed improvement you get from improving the update process. But if you're changing 90% of the data and you optimize the unload and load process, maybe you can make it faster. — Justin Cave
– Justin Cave, Commented Apr 12, 2021 at 12:43
You might want to consider a solution such as using views and generated columns to hide the data. That requires no changes to the actual data -- and updating large numbers of rows takes a looooooong time. — Gordon Linoff
– Gordon Linoff, Commented Apr 12, 2021 at 12:59
If you just need to remove some characters, no matter where they appear in the string, you should use REPLACE (not any regular expression functions; as to SUBSTR, I don't see how you would use it for this task). I don't think anything you can do will be faster than using REPLACE in the database. Same thing if you need to translate rather than remove; you can use the TRANSLATE function in the database. — user5683823
– user5683823, Commented Apr 12, 2021 at 14:05

D. Mika · Accepted Answer · 2021-04-12 15:36:00Z

3

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.

answered Apr 12, 2021 at 15:36

D. Mika

2,8282 gold badges18 silver badges34 bronze badges

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

1 Comment

satk0 Over a year ago

It's worth noting that in place of '_' there could be any other character. That one character's purpose is to avoid passing NULL (empty string is treated as null) as the third argument.

Maicon Santos · Accepted Answer · 2023-08-01 16:46:18Z

1

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

answered Aug 1, 2023 at 16:46

Maicon Santos

514 bronze badges

Collectives™ on Stack Overflow

Oracle : removing certain characters from string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related