Concatenating in a loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenating in a loop

Author

Topic

CVDpr
Starting Member

41 Posts

Posted - 2008-11-07 : 15:42:02

I loop the recordset and add "," between the numbers.


Dim sn, cdm As String

rsTemp.MoveFirst
While Not rsTemp.EOF
    sn = sn + "," + Format$(rsTemp("SampleNumber"))
    cdm = cdm + "," + Format$(rsTemp("CdmId"))    
    rsTemp.MoveNext
Wend

This = ",123,456,789" and then i do this to remove the first ","


Dim snF,cdmF As String

snF = "'" & Mid$(sn, 2, Len(sn)) & "'"
cdmF = "'" & Mid$(cdm, 2, Len(cdm)) & "'"

This = "123,456,789".

Is there a better way to add the "," between the numbers in the loop so i dont have to use the Mid$? or what i have is ok?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 15:49:49

sorry this is not a VB forum.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-07 : 15:59:18

[code]
rsTemp.MoveFirst
While Not rsTemp.EOF
sn = sn + Format$(rsTemp("SampleNumber")) + ","
cdm = cdm + Format$(rsTemp("CdmId")) ","
rsTemp.MoveNext
Wend
sn = right(sn,len(sn) -1)
cdm =right(cdn,len(cdm) -1)
[/code]

Please post in a proper forum next time.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-10 : 02:14:21


Dim sn, cdm As String

rsTemp.MoveFirst
While Not rsTemp.EOF
    sn = sn + IIf(sn = "", "", ",") + Format$(rsTemp("SampleNumber"))
    cdm = cdm +IIf(cdm = "", "", ",")  + Format$(rsTemp("CdmId"))    
    rsTemp.MoveNext
Wend

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 02:25:29

you can do this in T-SQL also

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114047

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-10 : 03:01:18