Avatar of Roebbelen
Roebbelen asked on

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

Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Roebbelen

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Louis01

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Roebbelen

Thanks I assume the Suff is my String spit function?
ASKER
Roebbelen

--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".
ASKER
Roebbelen

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck