CODEWITHSUNDEEP
sqlregexoracle-database
Ahmed Sol
Ahmed Sol

Reputation: 15

How to construct a specific regular expression

I want to create a regular expression that replaces every character in a string except the last 2 with a '*'. For example: 'abcdefgh' --> '******gh'

I am using oracle's regexp_replace, I have written something like:

regexp_replace('dfdfdfdfsdf','(.*)(..)','*\2',1,0)

but it ends up with one "*"

dfdfdfdfsdf --> *df

I would appreciate your kind assistance

Upvotes: 1

Views: 67

Answers (3)

MT0
MT0

Reputation: 167972

So long as you are not worried about 1 or 2 character strings then you can use the regular expression .(..$)?:

Query

WITH test_data ( value ) AS (
  SELECT NULL     FROM DUAL UNION ALL
  SELECT 'A'      FROM DUAL UNION ALL
  SELECT 'AB'     FROM DUAL UNION ALL
  SELECT 'ABC'    FROM DUAL UNION ALL
  SELECT 'ABCD'   FROM DUAL UNION ALL
  SELECT 'ABCDE'  FROM DUAL UNION ALL
  SELECT 'ABCDEF' FROM DUAL
)
SELECT value,
       REGEXP_REPLACE(
         value,
         '.(..$)?',
         '*\1'
       )
FROM   test_data

Outputs:

VALUE  | REGEXP_REPLACE(VALUE,'.(..$)?','*\1')
:----- | :------------------------------------
null   | null                                 
A      | *                                    
AB     | **                                   
ABC    | *BC                                  
ABCD   | **CD                                 
ABCDE  | ***DE                                
ABCDEF | ****EF

db<>fiddle here

Upvotes: 2

iqueiroz
iqueiroz

Reputation: 1

You can try replacing this pattern by *:

.(?=.{2})

Live example: https://regex101.com/r/uueD6B/1

Upvotes: 0

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

You can use LPAD.

select LPAD(SUBSTR('dfdfdfdfsdf',-2),LENGTH('dfdfdfdfsdf'),'*') from dual;

OUTPUT

*********df

CHECK LIVE DEMO HERE

Upvotes: 4

Related Questions