I have a requirement in oracle to replace the special characters at first and last position of the column data.

Requirement: only [][.,$'*&!%^{}-?] and alphanumberic characters are allowed to stay in the address data and rest of the characters has to be replaced with space.I have tried in below way in different probabilities but its not working as expected. Please help me in resolving this.

SELECT emp_address,
       REGEXP_REPLACE(
         emp_address,
         '^[^[[][.,$'\*&!%^{}-?\]]]|[^[[][.,$'\*&!%^{}-?\]]]$'
       ) AS simplified_emp_address
FROM   table_name

CodePudding user response：

As per the regular expression operators and metasymbols documentation:

• Put ] as the first character of the (negated) character group;
• - as the last; and
• Do not put . immediately after [ or it can be matched as the start of a coalition element [..] if there is a second . later in the expression.

Also:

• Double up the single quote (to escape it, so it does not terminate the string literal); and
• Include the non-special characters a-zA-Z0-9 in the capture group too otherwise they will be matched.

Which gives you the regular expression:

SELECT emp_address,
       REGEXP_REPLACE(
         emp_address,
         '^[^][,.$''\*&!%^{}?a-zA-Z0-9-]|[^][,.$''\*&!%^{}?a-zA-Z0-9-]$'
       ) AS simplified_emp_address
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (emp_address) AS
SELECT '"test1"' FROM DUAL UNION ALL
SELECT '$test2$' FROM DUAL UNION ALL
SELECT '[test3]' FROM DUAL UNION ALL
SELECT 'test4' FROM DUAL UNION ALL
SELECT '|test5|' FROM DUAL;

Outputs:

EMP_ADDRESS	SIMPLIFIED_EMP_ADDRESS
"test1"	test1
$test2$	$test2$
[test3]	[test3]
test4	test4
|test5|	test5

db<>fiddle here

CodePudding user response：

You do not need regular expressions, because they will have cumbersome escape sequences. Use substrings and translate function:

with a as (
  select
    'some [data ]' as val
  from dual
  union all
  select '{test $' from dual
  union all
  select 'clean $%&* value' from dual
  union all
  select 's' from dual
)
select
  translate(substr(val, 1, 1), q'{ [][.,$'*&!%^{}-?]}', ' ')
  || substr(val, 2, lengthc(val) - 2)
  || case
       when lengthc(val) > 1
       then translate(substr(val, -1), q'{ [][.,$'*&!%^{}-?]}', ' ')
     end
  as value_replaced
from a

| VALUE_REPLACED   |
| :--------------- |
| some [data       |
| test             |
| clean $%&* value |
| s                |

db<>fiddle here