CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mehmet
Mehmet

Reputation: 2278

replace all characters in string

I want to replace all characters in a string to "*" star character.

For example: 

NAME_SURNAME : ANDREY KIMBELL
REPLACED     : ****** *******

How can I do this in Oracle PL/SQL ?

Upvotes: 3

Views: 15966

Answers (5)

Peter M
Peter M

Reputation: 1

SELECT REGEXP_REPLACE('ANDREY KIMBELLasd123[]{}', '.', '*')
FROM dual;

Upvotes: -1

Boneist
Boneist

Reputation: 23578

An alternative to using regexp functions is to use the TRANSLATE function:

SELECT TRANSLATE('ANDREY KIMBELL1', 
                 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 
                 '****************************************************')
FROM   dual;

(I deliberately didn't use the UPPER functionality before doing the TRANSLATE since that introduces a second function into the mix, and would slow things down)

ETA: as someone attempted to point out by editing my answer, rather than commenting on it, it turns out that translate is slower than using the regexp_replace - looks like the difference I was seeing was due to context switching in pl/sql. Or something.

Anyway, the below demonstrates what I now see:

set timing on

create table timing_regexp_replace
as
SELECT REGEXP_REPLACE(owner||' '||table_name||' '||column_name, '[[:alpha:]]', '*') col1
from dba_tab_columns;

create table timing_translate
as
SELECT TRANSLATE(owner||' '||table_name||' '||column_name, 
                 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
                 '****************************************************') col1
from dba_tab_columns;

which returns:

Table created.
Elapsed: 00:00:00.96
Table created.
Elapsed: 00:00:02.87

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You could use the TRANSLATE function:

SELECT TRANSLATE(UPPER('Andrey Kimbell'),
                 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                 '**************************')
  FROM DUAL

Best of luck.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use:

Demo

SELECT REGEXP_REPLACE('ANDREY KIMBELL', '\w', '*')
FROM dual

where \w is the Alphanumeric characters plus _ equivalent of [A-Za-z0-9_].

Or if only letters:

SELECT REGEXP_REPLACE('ANDREY KIMBELL1', '[[:alpha:]]', '*')
FROM dual

Upvotes: 9

SomeJavaGuy
SomeJavaGuy

Reputation: 7347

you could to it like this

select regexp_replace('abdcde123fge','[A-Za-z]','*') 
from dual;

This replaces everything in the range of a-z and A-Z with a *

Upvotes: 3

Related Questions