Oracle query to find any special character in first position or end position of the field value-CodePudding

I have a table in Oracle database with special characters attached at first and last position in the field value. I want to eliminate those special characters while querying the table. I have used INSTR function but I had to apply for each and every special character using CASE expression. Is there a way to eliminate any special characters that is attached only at first and last positions in one shot?

The query I am using as is below: CASE WHEN INSTR(emp_address,'"')=1 THEN REPLACE((emp_address,'"', ''). . . .

CodePudding user response：

You can use regular expressions to replace the leading and trailing character of a string if they match the regular expression pattern. For example, if your definition of a "special character" is anything that is not an alpha-numeric character then you can use the regular expression:

^ the start-of-the-string then
[^[:alnum:]] any single character that does not match the POSIX alpha-numeric character group
| or
[^[:alnum:]] any single character that does not match the POSIX alpha-numeric character group then
$ the end-of-the-string.

Like this:

SELECT emp_address,
       REGEXP_REPLACE(
         emp_address,
         '^[^[:alnum:]]|[^[:alnum:]]$'
       ) AS simplified_emp_address
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (emp_address) AS
SELECT 'test' FROM DUAL UNION ALL
SELECT '"test2"' FROM DUAL UNION ALL
SELECT 'Not "this" one' FROM DUAL;

Outputs:

EMP_ADDRESS SIMPLIFIED_EMP_ADDRESS

test test

"test2" test2

Not "this" one Not "this" one

EMP_ADDRESS	SIMPLIFIED_EMP_ADDRESS
test	test
"test2"	test2
Not "this" one	Not "this" one

If you have a more complicated definition of a special character then change the regular expression appropriately.

db<>fiddle here