Accessing three comma deliminated variables in order...

[ranta]

I am passing three comma deliminated fields to my stored procedure, the first of which contains codes, the second two contain dates pertaining to the codes in the first.

They are all in order, is there a quick way of matching up this data into a temp table so I get on record for each code containing the code and dates 1 and 2?

The only way I can think of is using three different cursors to split each value out and assign them an ordering int so I can update the relevant codes with their dates...

 

[Jamfool]

DECLARE @IN as varchar(4000)
DECLARE @first_comma int
DECLARE @second_comma int
SELECT @IN = 'xaaax,xbbbx,xcccx'

SELECT @first_comma = PATINDEX ('%,%',@IN)
SELECT @second_comma = @first_comma + PATINDEX ('%,%',RIGHT(@IN,LEN(@IN)-@first_comma))

SELECT LEFT (@IN,@first_comma-1) as '1st'
,SUBSTRING (@IN,@first_comma+1,@second_comma-@first_comma-1) as '2nd'
,SUBSTRING (@IN,@second_comma+1,len(@in)) as '3rd'

 

[ranta]

Forgive me ignorance but what exactly does this achieve? from what I can understand of your code this is simply going to split out each code within @IN.. There could be any number of codes within this variable so I would still really have to loop round and I still have the problem of matching the codes up to the values in the other two varchar's....

 

[Jamfool]

I was workin along the lines that your data was as follows

part1,part2,part3~part1,part2,part3~part1,part2,part3

Therefore the bit of code I wrote would split the data into three bits as you said, so Iam not sure what you mean when you say you will have to match the code up, because you said they were in order. You will still need to specify a record marker. (~ example) So you can split each 3 piece record from the csv

 

[ranta]

OK, I think I see where you are coming from.

I was originaly going to send the detail as three seperate variables, i.e.

@code - '1,344,8877,3,144...'
@date1 - '01/01/03,02/01/03,03/06/04...'
@date2 - '10/02/03,02/04/03,25/07/03...'

From what I can understand of your code you are saying that the string would be better built in one,
i.e. '1,01/01/03,10/02/03~344,02/01/03,02/04/03...'

Am I understanding this correctly?

Thanks for your help on this...

 

[Jamfool]

yes thats what i was thinking.