DB version: 19c
I have a table with values like below.
I want to run a SELECT in which
Requirement 1: All double quotes are removed
Requirement 2: All trailing @JM$_API strings are removed
create table test_tab2(name varchar2(80));
insert into test_tab2 values('"J00HRTBSEM01"."J_P_BRDG_HRFLEX"@JM$_API');
insert into test_tab2 values('"J00FINXSEM01"."J_P_BRDG_FNFLEX"@JM$_API');
insert into test_tab2 values('"J00VXREFND03"."J_P_LWRK_MNTHLY"@JM$_API');
commit;
SQL> col NAME for a55
SQL> select * from test_tab2;
NAME
-------------------------------------------------------
"J00HRTBSEM01"."J_P_BRDG_HRFLEX"@JM$_API
"J00FINXSEM01"."J_P_BRDG_FNFLEX"@JM$_API
"J00VXREFND03"."J_P_LWRK_MNTHLY"@JM$_API
I achieved requirement 1 using REPLACE function.
col sanitized for a55
SELECT REPLACE (name, '"') as sanitized FROM test_tab2;
SANITIZED
-------------------------------------------------------
J00HRTBSEM01.J_P_BRDG_HRFLEX@JM$_API
J00FINXSEM01.J_P_BRDG_FNFLEX@JM$_API
J00VXREFND03.J_P_LWRK_MNTHLY@JM$_API
But, how can I remove the trailing @JM$_API string from the values (including the @ symbol). @JM$_API is a hard coded string.
So, the expected output would be
J00HRTBSEM01.J_P_BRDG_HRFLEX
J00FINXSEM01.J_P_BRDG_FNFLEX
J00VXREFND03.J_P_LWRK_MNTHLY