JOIN using LIKE in SQL

Q. Can I join tow tables using like (more flexible criteria) than exact match?

Ans. Yes

[sql]
— create test table first

create table #A (
id varchar(10)
)

create table #b(
number varchar(5)
)

insert into #A values(‘123’)
insert into #A values(‘456’)
insert into #A values(‘789’)
insert into #A values(‘0123’)
insert into #A values(‘4567′)

select * from #A

insert into #B values(’12’)
insert into #b values(’45’)
insert into #b values(‘987’)
insert into #b values(‘012’)
insert into #b values(‘666’)

— This query pulls data based on partial match

select * from #a, #b
where #a.id like ‘%’ + #b.number + ‘%’

[/sql]

Table A and B


-- Table A and B

+----------+  +---------+
| table A  |  | Table B |
+----------+  +---------+
|      123 |  |      12 |
|      456 |  |      45 |
|      789 |  |     987 |
|     0123 |  |     012 |
|     4567 |  |     666 |
+----------+  +---------+

Result


-- Result

+------+--------+
|  id  | number |
+------+--------+
|  123 |     12 |
| 0123 |     12 |
|  456 |     45 |
| 4567 |     45 |
| 0123 |    012 |
+------+--------+

Advertisements
This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s