How to use regexp_replace to replace a character in a string using the select statement?

Question

Following is my code

select (upper(fname)||' '||upper(mname)||' '||upper(lname)) as customer_name,
(indv_corp_flag) as Customer_type,
(regexp_replace(passport_no,'D','B')"regexp_replace") as passport_no, 
(case when indv_corp_flag = 'C' then registration_no else null end) as registration_no,
(case when indv_corp_flag = 'I' then marital_status else null end) as Marital_status
from Nbfc_krishnan_m;

it gives error like

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 91 Column: 38

But on operating individually it works like

select
regexp_replace(passport_no,'S','d')"regexp_replace" 
from nbfc_krishnan_m; 

is successfully executed.

Answer 1

You have wrapped all the columns of your projection in unnecessary brackets. It compiles but the clutter is preventing you from seeing the problem. Without those brackets it becomes clear you have a column with two aliases:

regexp_replace(passport_no,'D','B')"regexp_replace" as passport_no

You posted a working query. It works because it has only one alias. Hence the solution is obvious:

select upper(fname)||' '||upper(mname)||' '||upper(lname) as customer_name,
        indv_corp_flag as Customer_type,
        regexp_replace(passport_no,'D','B') as passport_no, 
        case when indv_corp_flag = 'C' then registration_no else null end as registration_no,
        case when indv_corp_flag = 'I' then marital_status else null end as Marital_status
from Nbfc_krishnan_m;

In future reserve brackets for expressions which actually need them.

---

" if the string is DCCFF12996 and I want output to be BCCFF12669"

You're just substituting one character for another, so you should use the standard TRANSLATE() function. Only use REGEXP_REPLACE() when you need a regular expression (i.e. a pattern).

SQL> select upper(fname||' '||mname||' '||lname) as customer_name,
  2     passport_no, 
  3     translate(passport_no,'D69','B96') as new_passport_no 
  4  from Nbfc_krishnan_m; 

CUSTOMER_NAME                     PASSPORT_N NEW_PASSPO
--------------------------------- ---------- ----------
FOX IN SOCKS                      ABCD123    ABCB123
DAISY HEAD MAYZIE                 DCCFF12996 BCCFF12669

SQL>