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

ASKER CERTIFIED SOLUTION

Louis01

membership

This solution is only available to members.

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

Start Free Trial

Roebbelen

ASKER

Thanks I assume the Suff is my String spit function?

Roebbelen

ASKER

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

Roebbelen

ASKER

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