SQL split field entry only returning first entry before a comma

tobzzz
tobzzz used Ask the Experts™
on
I'm using MS SQL Server 2008, I have a field called townName that could contain data like:
Lincoln
or like:
Lincoln,Boston,York

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:
SELECT TWN.id
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 :-)

Thanks experts!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
You can use any of the split function from following post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Which will store data in temp table which you can use to join data (using cursor) as data might vary in each column)

it seems that you only want the first entry before a comma, so this will do it. (it will also take into consideration those cases where there's no comma )
SELECT TWN.id 
FROM town TWN
    INNER JOIN otherTown OTH ON TWN.townName= left(OTH.townName, charindex(',', OTH.townname+',')-1)
WHERE TWN.active = 1

Open in new window

Author

Commented:
Unfortunately most of the info in that link is way beyond me, I'm fairly basic at SQL. I would prefer not to have to create temp tables, I was hoping for a function to split that field and do the look-up all in one SQL statement. Perhaps I could do a subselect to do this but I wouldn't know how. I need code provided, customising the SQL I posted so I can understand what's going on, links make it all the more difficult. Many thanks!
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

have you tried my suggestion? you mentioned that you only wanted the first entry before the comma. Can you please clarify?
If what you want is to compare with all towns, Lincoln, Boston and York, then create the function attached below and use it in your query like this:
select a.Title, b.Value as Material_number, [Business Group], Email
from town a
inner join ( select t2.Value as townname from
  othertown t1
  cross apply dbo.Parmstolist(t1.townname, ',') t2  
) b on a.townname = b.townname
 

CREATE FUNCTION [dbo].[ParmsToList] (@Parameters varchar(8000), @delimiter varchar(10) )  
returns @result TABLE (Value varchar(200))  
AS    
begin  
    declare @dx varchar(9)  
    -- declare @loops int  
     --set @loops = 0  
   
     DECLARE @TempList table  
          (  
          Value varchar(200)  
          )  
   
     if @delimiter is null  set @delimiter = ' '  
     if len(@delimiter) < 1 set @delimiter = ' '  
     set @dx = left(@delimiter, case when @delimiter = ' ' then 1 else len(@delimiter) end -1)  
   
     DECLARE @Value varchar(8000), @Pos int  
   
     SET @Parameters = LTRIM(RTRIM(@Parameters))+ @delimiter  
     SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)  
   
     IF REPLACE(@Parameters, @delimiter, @dx) <> ''  
     BEGIN  
          WHILE @Pos > 0 -- AND @Loops < 100  
          BEGIN  
               --set @loops = @loops + 1  
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))  
               IF @Value <> ''  
               BEGIN  
                    INSERT INTO @TempList (Value) VALUES (CAST(@Value AS varchar)) --Use Appropriate conversion  
               END  
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ case when @delimiter = ' ' then 1 else len(@delimiter) end, 8000)  
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)  
   
          END  
     END      
     INSERT @result  
     SELECT value  
        FROM @TempList  
     RETURN  
END

Open in new window

Author

Commented:
@ralmada: Sorry, I our posts crossed and didn't see you're until after mine, I'm just trying your code and yes you are correct, I only want the first entry before the comma so hopefully your first post will do it - one sec whilst I apply it to my context :-)

Author

Commented:
ralmada that was EXACTLY what I was looking for, a simple neat function/bit of syntax wrapped around my join. Thank you very much :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial