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 |
|
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 |
|
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 #669876 is a reply to message #669874] |
Mon, 21 May 2018 08:40 |
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 #669879 is a reply to message #669873] |
Mon, 21 May 2018 11:09 |
|
Michel Cadot
Messages: 68693 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 |
|
Michel Cadot
Messages: 68693 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
|
|
|
Goto Forum:
Current Time: Sat Sep 28 08:08:58 CDT 2024
|