asked on

SQL split field entry only returning first entry before a comma

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!

Om Prakash

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)

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tobzzz

ASKER

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!

ralmada

have you tried my suggestion? you mentioned that you only wanted the first entry before the comma. Can you please clarify?

ralmada

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

tobzzz

ASKER

@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 :-)

tobzzz

ASKER

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