troubleshooting Question

TSQL Looping thru a table spliting strings

Avatar of Roebbelen
Roebbelen asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
4 Comments1 Solution1087 ViewsLast Modified:
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   

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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros