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.
You need the wildcard on the right side of the LIKE
.
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.