I'm using MS SQL Server 2008, I have a field called townName that could contain data like:
I have an inner join between two tables and basically I want the join to be based on the first part of the field, before the first comma.
My SQL at the moment is:
FROM town TWN
INNER JOIN otherTown OTH ON TWN.townName= OTH.townName
WHERE TWN.active = 1
but it's not getting a result because it's effectively saying:
INNER JOIN otherTown OTH ON 'Lincoln' = 'LIncoln,Boston,York'
So how do I split OTH.townName to the bit before the first comma only and rememebering that it might not contain a comma if there's only one town entered into the otherTown field.
I hope that makes sense :-)