Home » SQL & PL/SQL » SQL & PL/SQL » How to implement regex (merged 2)
How to implement regex (merged 2) [message #669872] Mon, 21 May 2018 04:58 Go to next message
purnima1
Messages: 79
Registered: June 2014
Member
Hi Team ,

I have one lookup table which lest says have following data :

create table Gartner_Account_End_Strip_List (token varchar2(4000), return_value  varchar2(4000), status  varchar2(10) ,added_by varchar2(100), add_dt date, upd_by varchar2(100), upd_dt date);
begin 
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT BARTHELEMY','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT HELENA','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT KITTS','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT LUCIA','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT MARTIN ','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT PIERRE AND MIQUELON','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT VINCENT AND THE GRENADINES','','pubhatia',sysdate);
commit;

Now I have one input data "IBM Company Saint vincent" .
Now I need to lookup the data in lookup table and remove SAINT VINCENT AND THE GRENADINES with NULL as it matches with 'SAINT VINCENT AND THE GRENADINES'

IBM Company Saint >>> IBM Company
How to implement regex [message #669873 is a reply to message #669872] Mon, 21 May 2018 04:59 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
Hi Team ,

I have one lookup table which lest says have following data :

create table Gartner_Account_End_Strip_List (token varchar2(4000), return_value  varchar2(4000), status  varchar2(10) ,added_by varchar2(100), add_dt date, upd_by varchar2(100), upd_dt date);
begin 
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT BARTHELEMY','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT HELENA','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT KITTS','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT LUCIA','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT MARTIN ','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT PIERRE AND MIQUELON','','pubhatia',sysdate);
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT VINCENT AND THE GRENADINES','','pubhatia',sysdate);
commit;

Now I have one input data "IBM Company Saint vincent" .
Now I need to lookup the data in lookup table and remove SAINT VINCENT AND THE GRENADINES with NULL as it matches with 'SAINT VINCENT AND THE GRENADINES'

IBM Company Saint >>> IBM Company
Re: How to implement regex [message #669874 is a reply to message #669873] Mon, 21 May 2018 05:32 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
Basically I want to find LONGEST VALUE ENDING SUB STRING IN ORACLE
Re: How to implement regex [message #669876 is a reply to message #669874] Mon, 21 May 2018 08:40 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I don't know what you are asking. What does finding the longest have anything to do with removing a value that already exists? And why remove the one you stated, because IBM does not match the one you listed. Please give a coherent explanation of what you want.
Re: How to implement regex [message #669877 is a reply to message #669876] Mon, 21 May 2018 09:05 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
Hi All,

I just want to iterate through lookup table and find longest matching sub-string from End of string. And then replace that sub-string with Null for input value.

[Updated on: Mon, 21 May 2018 09:05]

Report message to a moderator

Re: How to implement regex [message #669879 is a reply to message #669873] Mon, 21 May 2018 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> create table Gartner_Account_End_Strip_List (token varchar2(4000), return_value  varchar2(4000), status  varchar2(10) ,added_by varchar2(100), add_dt date, upd_by varchar2(100), upd_dt date);

Table created.

SQL> begin
  2  insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT BARTHELEMY','','pubhatia',sysdate);
  3  insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT HELENA','','pubhatia',sysdate);
  4  insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT KITTS','','pubhatia',sysdate);
  5  insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT LUCIA','','pubhatia',sysdate);
  6  insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT MARTIN ','','pubhatia',sysdate);
  7  insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT PIERRE AND MIQUELON','','pubhatia',sysdate);
  8  insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT VINCENT AND THE GRENADINES','','pubhatia',sysdate);
  9  commit;
 10  end;
 11  /
insert into xxgmdmadm.Gartner_Account_End_Strip_List (token , return_value,added_by,add_dt) values (' SAINT BARTHELEMY','','pubhatia',sysdate);
                      *
ERROR at line 2:
ORA-06550: line 2, column 23:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 3, column 23:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 4, column 23:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 23:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 23:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 7, column 23:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 23:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 8, column 1:
PL/SQL: SQL Statement ignored
Re: How to implement regex (merged 2) [message #669906 is a reply to message #669872] Wed, 23 May 2018 08:17 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    input as (select '&input' input from dual),
...
 18  /
Enter value for input: IBM Company Saint vincent
RESULT
------------------------------
IBM Company

SQL> /
Enter value for input: IBM Company Saint
RESULT
------------------------------
IBM Company

SQL> /
Enter value for input: IBM Company
RESULT
------------------------------
IBM Company

SQL> /
Enter value for input: IBM
RESULT
------------------------------
IBM

[Updated on: Wed, 23 May 2018 08:18]

Report message to a moderator

Previous Topic: Oracle Role creation on Schema
Next Topic: connect by prior performance issue
Goto Forum:
  


Current Time: Thu Mar 28 17:32:22 CDT 2024