Join Tables by a Field that contains a delimited string

This is the first time I ran into a situation where I needed to Join two tables (one with a varchar field and one with a varchar field that contains pipe delimited data) so I decided to post it for other who may not know you can do this. Below is a simplified example with solution of getting all rows of the first table whos values are contained in the pipe delimited field of the second table. This is really simple to do and you don’t even have to use any User-Defined Functions or Cursors to do it. And I’m sure that not all of you who may read this know that you can use LIKE in a JOIN.

1 SELECT City
2 FROM Table1
3 JOIN Table2 ON Table2.CityList LIKE ‘%’ + Table1.City + ‘%’

Table1
City varchar(30)

Table1 Sample Data
West Bend
Kewaskum
Hartford

Table2
CityList varchar(2000)

Table2 Sample Data
West Bend|Kewaskum
Kewaskum
Hartford

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s