t-sql, sql table inner join spreadsheet

Question

Hi all,

I have a table (AmenityData) of data and a column of this table contains postalsectors e.g. E14 7

I also have an Excel spreadsheet with a list of postal districts e.g. E14

I need to get all the data out of the AmenityData table where the postal districts are LIKE the postal sectors e.g. WHERE [PostalDistricts] + '%' LIKE [PostalSector].

The code i'm using at the moment is not coming up with an error but just returning nothing and i know that there should be plenty of results returned:

SELECT * FROM AmenityData As a
INNER JOIN  OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\Bdzserver\db_creation\postaldistricts.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') As b
ON b.[PostalDistricts] + '%' LIKE a.[PostalSector]

I'm not even sure if you can join tables using a LIKE, i've never done this before...

Thanks for any help.

J.

Answer 1

I've managed to sort this out myself the long way round.

I just created a new column in the data and added the postalsectors to this column, then converted the postal sectors to postal districts

UPDATE [AmenityData]
SET PostalDistrict = REPLACE(PostalDistrict , ' ', '')

UPDATE [AmenityData]
SET PostalDistrict = LEFT(PostalDistrict ,LEN(PostalDistrict )-1)

Once this was done I ran the following

SELECT * FROM AmenityData As a
INNER JOIN  TypesToGroups As b
ON a.ClassCode =  b.FacilityTypeID
INNER JOIN Groups As c
ON b.GroupID = c.GroupID
INNER JOIN  OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\Bdzserver\db_creation\postaldistricts.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') As d
ON d.[PostalDistricts] = a.[PostalDistrict]

and all worked fine.

I would still like to know if my original question is possible though if anyone can help.

J.

Answer 2

You need the wildcard on the right side of the LIKE.