How to parse a comma delimited string 1

[mGis]

Hello everyone,

I've got another strange one for you.(using SQL Server 7.0)

Is it possible to break apart a string that is delimited by comma's?

User Fred  --> Ans_String_Val = '0,1,2,3,4'
User Wilma --> Ans_String_Val = '6,5,1,2,0'

A)I want to put the values in between the commas into seperate columns, for all users.(for exporting to an Excel Report)

Users     A1     A2     A3     A4     A5
 Fred     0      1      2      3      4
Wilma     6      5      1      2      0     

B) I want to add the numbers
      Fred = 10 -->(0+1+2+3+4)
     Wilma = 14 -->(6+5+1+2+0)

Thanks, Michael

 

[tlbroadbent]

Check the following link to download a stored procedure for parsing a comma delimited variable. You should be able to modify it for your use.

http://www.sqlteam.com/item.asp?ItemID=637

Terry

http://members.home.net/tlbroadbent/index.htm

 

[redlam]

i've actually had to do something like this in the past... NOTE: this only works if your Ans_String_Val ends in a comma (sorry, got lazy in the end)
anyway, try this:
create table NewAnswerTable
(UserName varchar(25),
Answer1 int,
Answer2 int,
Answer3 int,
Answer4 int,
Answer5 int)

declare @Start int
declare @Column int
declare @AnswerVal varchar(5)
declare @UserName varchar(25)
declare @SQL varchar(5000)
declare curUserName cursor for
select UserName
from AnswerTable
open curUserName
fetch next from curUserName into @UserName
While @@fetch_status = 0

begin
select @Start = 1,
@Column = 1
While @Start < (select len(Ans_String_Val) from AnswerTable where UserName = @UserName)
begin
select @AnswerVal = substring(Ans_String_Val,@Start,charindex(',',Ans_String_Val,@Start)-@Start)
from AnswerTable
where UserName = @UserName
if not exists (select UserName from NewAnswerTable where UserName = @UserName)
begin
select @SQL =
'insert into
NewAnswerTable
(UserName, Answer' + convert(varchar,@Column) + ')
values (' + ''''+ @UserName +''''+ ',' + @AnswerVal + ') '
end
else
begin
select @SQL =
'update NewAnswerTable
set Answer' + convert(varchar,@Column) + '=' + @AnswerVal + '
where UserName = ' + '''' + @UserName + ''''
end

select @Start = charindex(',',Ans_String_Val, @Start) + 1
from AnswerTable
where UserName = @UserName
select @Column = @Column + 1

exec(@SQL)
end

fetch next from curUserName into @UserName
end
close curUserName
deallocate curUserName

 

[mGis]

tlbroadbent,

I think that is exactly what I'm looking for,
but unfortunately I'm BRAND NEW to SP's and have spent the past few days reading BOL, without luck.

Could you show me an example of how to Call or execute the sp from a query
I'm assuming(but rather clueless)that I have to pass the parameters:
@array = Ans_String_VAl,
@separator = ',' with something like(from BOL):
[[EXEC[UTE]]
{
[@return_status =]
{procedure_name [;number] | @procedure_name_var
}
[[@parameter =] {value | @variable [OUTPUT] | [DEFAULT]]
[,...n]
[WITH RECOMPILE]

P.S.:
Redlam,
my Ans_String_Val does not end in a comma, but thanks for the idea.

 

[tlbroadbent]

Redlam,

You are very close. BOL always makes it look so difficult.

Simple SP execute:

exec MySP @array, @separator

This is all you need to do if the SP will do all the work of parsing and updating the table. If you want the procedure to return a status you can use the @return_status...

declare @rs int
exec @rs = MySP @array, @separator

Inside the SP you could set the status with the Return statement, such as Return 1.

In addition, you can return values via OUTPUT parameters.

declare @rs int
exec @rs = MySP @array, @separator, @outparam OUTPUT

Good luck. Terry

http://members.home.net/tlbroadbent/index.htm

 

[mGis]

I got the stored procedure to work as a stand alone procedure, but it is not exactly what I am looking for.
what I have is a list of questions answered by users:

User    A_1       A_2       A_3       A_n
101     0,0,0,0   1         1         0
22      1,2,3,4   5         3         1
3110    0,1,2,1   7         <NULL>    1

I can do a cross-tab report to group the users answers(above),
but now I need to find a work around to break out the string:

User    A1_1   A1_2   A1_3   A1_4     A_2     A_3     A_n
101     0      0      0      0        1       1       0
22      1      2      3      4        5       3       1
3110    0      1      2      1        7       <NULL>  1

Is this something that can be done is SQL server 7.0, would Crystal Reports be able to help with this work-around.
This was the design of the database(works great for input, but poor for reporting).
If I could get this problem solved, I may not have to resort to Crystal or other 3rd party software, or a re-design of ASP web code and database.
So I guess my question is can you call a _sp from within a query?
Select
User,
EXEC {spParse_Array Ans_String_Val_1,','},
Ans_String_Val_2,
ans_String_Val_n
FROM tblAnswers
WHERE Blah, Blah, Blah;


Where Ans_String_Val_1 would be the value of Ans_String_Val. When I tried something like this I get the following message:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

_sp needs to be global enough to re-use for other tables, values, amount of strings to be parsed!!!!A1_1 ... A1_n (where n may be 2 other n's up to 20 or so)
Any Ideas?
Michael

 

[tlbroadbent]

Sorry, but you cannot execute a SP inside a query as you would like. Terry

http://members.home.net/tlbroadbent/index.htm

 

[mGis]

Terry,
That's what I was afraid of, but thanks for the help.
It gave a better understanding of _sp's, so it wasn't a waste.

Can anybody see a possible MIRACLE work-around?,
Michael