Split Delimited FIeld; Assign a month based on position

[MrsMope987]

Hello,
I have a field that has 13 values in it separated by a semi-colon. Each value = a month, the 13th is for year-end adjustments. The table housing this field is stored in a PROGRESS database, I'm using the following SQL function to split these values into unique rows. I have SQL 2005;

ALTER FUNCTION [dbo].[ParseValues]  
	(@String varchar(8000),
	@Delimiter char(1)  
	)  
		RETURNS @RESULTS TABLE  
		(ID int identity(1,1),  
		Val varchar(1000)
		)  
		AS  
		
			BEGIN  

			DECLARE @Value varchar(100)  
			WHILE @String is not null  
		
			BEGIN  
		
				SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 
					THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) 
					ELSE @String 
				END,  

				@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 
					THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) 
					ELSE NULL 
				END  
				
				INSERT INTO @RESULTS (Val)   
					SELECT @Value   
			END  
	RETURN  
END

THis all works great, but I want to add an additonal column to my results table called 'Mnth' which corresponds to the position of the value in the field. IE: first value=1 in Mnth, second Value= 2in mnth. How do I do this?

Finally I'd like to take this function and make it so I can permanetly store this table in SQL because once I get this done I need to add a few more things to the data (Acct Descrp etc) and then use SQL Reporting to design a report.

 

[gmmastros]

Have you looked at the ID column returned from this function. It think that will do what you want.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MrsMope987]

yes, but does the ID column reset each time a new @string is looked at? That's what I need for each @ string that's looped through I need the Month field to reset and begin at 1 again until that field is delimited, then reset to 1 again for the next field.

 

[gmmastros]

The ID Column is in a temp table, so... yes. It is reset each time you call the function. If you show some sample code where you use this function, we may be able to help you more.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MrsMope987]

Here is a query that I use to access this function:

SELECT G.yr,
	G.GLDivNo,
	G.GLDeptNo,
	G.GLAcctNo,
	G.GLSubNo,
	G.GLTitle,
	F.Mnth,
	F.Val
FROM PROGRESS..PUB.GLSA G 

CROSS APPLY dbo.ParseValues(G.peramt,';')F
WHERE G.Cono=1 AND G.yr=9

I want another column in the temp table called Mnth that houses what position in the PerAmt field the value is from. so in the above the column Mnth would be 1-13 for each row I show.

Here is the data I get back

Yr   GLDIVNO  GLDEPTNO GLACCTNO GLSUBNO Title           VAL
9	10	79	7654	0	MANAGEMENT INC	1500
9	10	79	7654	0	MANAGEMENT INC  1500
9	10	79	7654	0	MANAGEMENT INC  1500
9	10	79	7654	0	MANAGEMENT INC  1500
9	10	79	7654	0	MANAGEMENT INC  7500
9	10	79	7654	0	MANAGEMENT INC  1500
9	10	79	7654	0	MANAGEMENT INC  1500
9	10	79	7654	0	MANAGEMENT INC  1500
9	10	79	7654	0	MANAGEMENT INC  0
9	10	79	7654	0	MANAGEMENT INC  0
9	10	79	7654	0	MANAGEMENT INC  0
9	10	79	7654	0	MANAGEMENT INC  0
9	10	79	7654	0	MANAGEMENT INC  0

That would be one record split out here is the original data/field:

Yr   GLDIVNO  GLDEPTNO GLACCTNO GLSUBNO Title           
9	10	79	7654	0	MANAGEMENT INC 
PerAmt
1500;1500;1500;1500;7500;1500;1500;1500;0;0;0;0;0