Pattern Joins in Oracle Using Like Operator

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 B output:

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:

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:

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’;

Matching Table A address pattern in Table B address:

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

Output:

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||’%’;

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