Pattern Joins in Oracle Using Like Operator

Sep 10, 2019

Share this post

The Like operator is used to search the pattern in one table. Similarly, we can use the like operator along with the concatenation function to do a pattern matching between the multiple tables. Here I have given example for two tables.

Examples:

Here I have created two tables named as A,B with few fields and inserted one row in table A and 4 rows in table B.

create table A(no number(10),name varchar(30),address varchar(100));

create table B(address varchar(100),pincode varchar(10));

insert into A values(1,’A’,’Hyderabad’);

insert into B values(‘Hyderabad’,’500081′);

insert into B values(‘Hyderabad1′,’500082’);

insert into B values(‘1Hyderabad’,’500083′);

insert into B values(‘1Hyderabad1′,’500084’);

Table A output:

Table A

No Name Address
1 A Hyderabad

Table B output:

Table B

Address Pincode
Hyderabad 500081
Hyderabad1 500082
1Hyderabad 500083
1Hyderabad1 500084

 

Matching Table A address pattern in Table B address:

Below query performs pattern matching between table A with Table B.

select * from A,B where B.address like ‘%’||A.address||’%’;

Output:

Table A Table B
No Name Address Address Pincode
1 A Hyderabad Hyderabad 500081
1 A Hyderabad Hyderabad1 500082
1 A Hyderabad 1Hyderabad 500083
1 A Hyderabad 1Hyderabad1 500084

 

Here Table A address is ‘Hyderabad’ and it has the match with all the rows. So, it returned all the rows.

Matching Table B address pattern in Table A address:

select * from A,B where A.address like ‘%’||B.address||’%’;

Output:

Table A Table B
No Name Address Address Pincode
1 A Hyderabad Hyderabad 500081

 

Here Table B has 4 addresses and it has the one row match with A table. So, it returned one row.

Ex2:

Update Table A set Address=’ 1Hyderabad1’;

Table A

No Name Address
1 A 1Hyderabad1

 

Table B

Address Pincode
Hyderabad 500081
Hyderabad1 500082
1Hyderabad 500083
1Hyderabad1 500084

 

Matching Table A address pattern in Table B address:

select * from A,B where B.address like ‘%’||A.address||’%’;

Output:

Table A Table B
No Name Address Address Pincode
1 A 1Hyderabad1 1Hyderabad1 500081

Here Table A address is ‘1Hyderabad1’ and it has the match with one row. So, it returned one row.

 

Matching Table B address pattern in Table A address:

select * from A,B where A.address like ‘%’||B.address||’%’;

Table A Table B
No Name Address Address Pincode
1 A 1Hyderabad1 Hyderabad 500081
1 A 1Hyderabad1 Hyderabad1 500082
1 A 1Hyderabad1 1Hyderabad 500083
1 A 1Hyderabad1 1Hyderabad1 500084

Here Table B address has 4 rows and it has the match with all the rows. So, it returned all the rows.