TSQL Looping thru a table spliting strings

I have a table that I want to split out a string

Geocode, SFS
0354, John/Joe/Bill
0403, Sky
6795, Jason/Bill

I want to separate the names into separtate rows
0354, John
0354, Joe
0354, Bill
0403, Sky
6795, Jason
6795, Bill

I have a split Function that splits strings, however I'm struggling with how to loop through the table and join it back up with the Geocode...

CREATE FUNCTION dbo.Split3 ( @strString varchar(4000)) 
RETURNS  @Result TABLE(Value BIGINT) 
AS 
BEGIN 
     DECLARE @x XML  
	   SELECT @x = CAST('<A>'+ REPLACE(@strString,'/','</A><A>')+ '</A>' AS XML) 
       INSERT INTO @Result             
      
	  SELECT t.value('.', 'int') AS inVal 
      FROM @x.nodes('/A') AS x(t) 
    RETURN 
END    
GO   

Open in new window


The following Sample Code  is what I want using a cross apply to other existing tables howerver currently its not working...

 select [GEOMapCode],SFS from [dbo].[MapData]

;with cte as (SELECT GeoMapCode, dbo.Split3('.'/ 'VARCHAR(200)') AS series
FROM (SELECT [GEOMapCode], CAST ('<M>' + REPLACE(series, '/', '</M><M>') + '</M>' AS XML) AS String
	FROM  dbo.MAPDATA) AS A
CROSS APPLY String.nodes ('/M') AS Split(a))
select row_number() over(order by [GEOMapCode], series) as id, [GEOMapCode], series
from cte
ORDER BY 1,2

Open in new window

RoebbelenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Louis01Commented:
--Prepare the data
declare @IhaveAtable table (GeoCode varchar(4), SFS varchar(max));
insert into @IhaveAtable values ('0354', 'John/Joe/Bill');
insert into @IhaveAtable values ('0403', 'Sky');
insert into @IhaveAtable values ('6795', 'Jason/Bill');

--Do the work
with tmp(GeoCode, DataItem, Data) as (
    select GeoCode
         , LEFT(SFS, CHARINDEX('/',SFS+'/')-1)
         , STUFF(SFS, 1, CHARINDEX('/',SFS+'/'), '')
    from @IhaveAtable t1
    union all
    select GeoCode
         , LEFT(Data, CHARINDEX('/',Data+'/')-1)
         , STUFF(Data, 1, CHARINDEX('/',Data+'/'), '')
    from tmp
    where Data > ''
)
select GeoCode, DataItem
  from tmp
 order by GeoCode

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RoebbelenAuthor Commented:
Thanks I assume the Suff is my String spit function?
0
RoebbelenAuthor Commented:
--Do the work
with tmp(GeoMapCode, DataItem, Data) as (
    select GeoMapCode
         , LEFT(SFS, CHARINDEX('/',SFS+'/')-1)
         , STUFF(SFS, 1, CHARINDEX('/',SFS+'/'), '')
    from dbo.MapData t1
    union all
    select GeoMapCode
         , LEFT(Data, CHARINDEX('/',Data+'/')-1)
         , STUFF(Data, 1, CHARINDEX('/',Data+'/'), '')
    from tmp
    where Data > ''
)
select GeoMapCode, DataItem
  from tmp
 order by GeoMapCode 

Open in new window


I get the error...

Level 16, State 1, Line 2
Types don't match between the anchor and the recursive part in column "DataItem" of recursive query "tmp".
0
RoebbelenAuthor Commented:
Thank you

Ok I got it.. It worked with the following. I realized I had some duplicate data in my table.
Thanks a million


--Prepare the data
declare @IhaveAtable table (GeoMapCode nvarchar(255), SFS nvarchar(max));
INSERT INTO @IhaveAtable (GeoMapCode, SFS) (Select GeoMapCode, SFS FROM dbo.MapData GROUP By GeoMapCode, SFS);
--insert into @IhaveAtable values ('0403', 'Sky');
--insert into @IhaveAtable values ('6795', 'Jason/Bill');

--Do the work


with tmp(GeoMapCode, DataItem, Data) as (
    select GeoMapCode
         , LEFT(SFS, CHARINDEX('/',RTRIM(LTRIM(SFS))+'/')-1)
         , STUFF(SFS, 1, CHARINDEX('/',RTRIM(LTRIM(SFS))+'/'), '')
    from @IhaveAtable t1 WHERE SFS is not null
    union all
    select GeoMapCode
         , LEFT(Data, CHARINDEX('/',RTRIM(LTRIM(Data))+'/')-1)
         , STUFF(Data, 1, CHARINDEX('/',RTRIM(LTRIM(Data))+'/'), '')
    from tmp
    where Data > ''
)
select GeoMapCode, DataItem
  from tmp
 -- Where GeoMapCode='51610'
  Group by GeoMapCode, DataItem
 order by GeoMapCode
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.