Home » SQL & PL/SQL » SQL & PL/SQL » Regular Expression (Oracle 11.2.0.3)
Regular Expression [message #643469] Thu, 08 October 2015 04:00 Go to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
 
SQL> create table t_lic(id varchar2(20));

Table created.

SQL> create table t_temp (id varchar2(20));

Table created.

SQL> insert into t_lic values ('PA1234');

1 row created.

SQL> insert into t_lic values ('NJ4444');

1 row created.

SQL> insert into t_lic values ('5555');

1 row created.

SQL> insert into t_lic values ('6666D');

1 row created.

SQL> insert into t_lic values ('8888B');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t_temp values ('1234');

1 row created.

SQL> insert into t_temp values ('123');

1 row created.

SQL> insert into t_temp values ('23');

1 row created.

SQL> insert into t_temp values ('6666');

1 row created.

SQL> insert into t_temp values ('666D');

1 row created.

SQL> insert into t_temp values ('PA1234');

1 row created.

SQL> insert into t_temp values ('PA666D');

1 row created.

SQL> insert into t_lic values ('PA9999D');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t_temp values ('4444');

1 row created.

SQL> commit;

Commit complete.


SQL> insert into t_temp values ('9999');

1 row created.

SQL> commit;

Commit complete.


SQL> select * from t_lic order by 1;

ID                                                                                                  
--------------------                                                                                
5555                                                                                                
6666D                                                                                               
8888B                                                                                               
NJ4444                                                                                              
PA1234                                                                                              
PA9999D                                                                                             

6 rows selected.

SQL> select * from t_temp order by 1;

ID                                                                                                  
--------------------                                                                                
123                                                                                                 
1234                                                                                                
23                                                                                                  
4444                                                                                                
6666                                                                                                
666D                                                                                                
9999                                                                                                
PA1234                                                                                              
PA666D                                                                                              

9 rows selected.


There are two tables i.e. 1. t_lic 2.t_temp
t_lic and t_temp stores ids.
t_temp stores cleansed IDs eiether trimmed by first two or last character of t_lic id or may not be trimmed at all.

I would like to list all the t_temp ids which are matching to t_lic.
ID from t_temp is equal to t_lic when id from t_temp is
1. is exactly same of t_lic (or)
2. after prefixing NJ or PA (or)
3. after suffixing D or A (or)
4. after prefixing NJ or PA and after suffixing D or A

e.g. t_lic has NJ4444 which is equal to 4444 from t_temp after prefixing NJ -- rule 2 from above
so 4444 need to be pulled
e.g. t_lic has PA1234 which is equal to 1234 from t_temp after prefixing PA
e.g. PA9999D is equal to 9999 after prefixing PA and suffixing D to 9999 from t_temp

I have achieved this using "select , case and replace", however, can you please help me in resoving this using regular expression.

Regards,
Pointers

Report message to a moderator

Re: Regular Expression [message #643477 is a reply to message #643469] Thu, 08 October 2015 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Once again, create a test case, test it and post it but do not post its execution.
The purpose is we are able to create the same test case just copying and pasting what you write, with such execution it is not possible:
SQL> create table t_lic(id varchar2(20));

Table created.

SQL>
SQL> Table created.
SP2-0734: unknown command beginning "Table crea..." - rest of line ignored.
SQL>
SQL> SQL> create table t_temp (id varchar2(20));
SP2-0734: unknown command beginning "SQL> creat..." - rest of line ignored.
SQL>
SQL> Table created.
SP2-0734: unknown command beginning "Table crea..." - rest of line ignored.
SQL>
SQL> SQL> insert into t_lic values ('PA1234');
SP2-0734: unknown command beginning "SQL> inser..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>
SQL> 1 row created.
SQL>

You see what I mean?

Report message to a moderator

Re: Regular Expression [message #643480 is a reply to message #643469] Thu, 08 October 2015 06:12 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
I got you Micheal, sorry for the inconvenience.

 
 create table t_lic(id varchar2(20));

 create table t_temp (id varchar2(20));

 insert into t_lic values ('PA1234');
 insert into t_lic values ('NJ4444');
 insert into t_lic values ('5555');
 insert into t_lic values ('6666D');

 insert into t_lic values ('8888B');
 insert into t_temp values ('1234');
 insert into t_temp values ('123');
 insert into t_temp values ('23');
 insert into t_temp values ('6666');
 insert into t_temp values ('666D');
 insert into t_temp values ('PA1234');
 insert into t_temp values ('PA666D');
 insert into t_temp values ('4444');
 insert into t_lic values ('PA9999D');
 insert into t_temp values ('9999');
 
 commit;

select * from t_lic;

ID
--------------------
PA1234
NJ4444
5555
6666D
8888B
PA9999D

6 rows selected.

SQL> select * from t_temp;

ID
--------------------
1234
123
23
6666
666D
PA1234
PA666D
4444
9999

9 rows selected.

Report message to a moderator

Re: Regular Expression [message #643492 is a reply to message #643480] Thu, 08 October 2015 09:32 Go to previous messageGo to next message
bugfox
Messages: 18
Registered: October 2010
Junior Member
 
select *
from t_temp t
where exists (select 1
              from t_lic l
              where regexp_replace(l.id, '^(NJ|PA)|(D|A)$') = t.id)

Report message to a moderator

Re: Regular Expression [message #643611 is a reply to message #643492] Tue, 13 October 2015 09:27 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
As a good training tool and a real help to write regular expressions I use Expresso.

see: http://www.ultrapico.com/Expresso.htm

Report message to a moderator

Previous Topic: How To See Latest created Table Including Time in oracle. Is there any table for it?
Next Topic: Requirement to extract sql script output and attach the same in a email and send to multiple users
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jul 17 21:38:26 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.