String concatenation problem

• devvw

  SSC Enthusiast

  Points: 153

  More actions

  January 21, 2003 at 1:24 am

  #80673

  I'm trying to retrieve the list of drives where each db is created by selecting in the sysfiles. This gives me a resultset (x rows) but I would like to concatenate each rows in only one string. For example, the DB 'TEST' created on the drives E, F & G : The query will return me 3 rows and I would like to concatenate in a string looking like 'E;F;G'. The problem is that apparently it is not possible to concatenate the content of char variables in a loop.

  Can someone help ? Thanks in advance.

  DECLARE UsedDrives CURSOR FOR

  select distinct(rtrim(upper(substring(filename,1,1))))+';' from sysfiles

  OPEN UsedDrives

  DECLARE @Drives char(1)

  DECLARE @UsedDrives char(20)

  FETCH NEXT FROM UsedDrives INTO @Drives

  WHILE (@@FETCH_STATUS = 0)

  BEGIN

  Select @UsedDrives = @useddrives + @Drives

  Print @drives

  print @useddrives

  FETCH NEXT FROM UsedDrives INTO @Drives

  END

  CLOSE UsedDrives

  DEALLOCATE UsedDrives

  GO

• RicardoRobles

  Valued Member

  Points: 73

  More actions

  January 21, 2003 at 1:59 am

  #446823

  ok. Try:

  ..

  set @useddrives=''

  WHILE (@@FETCH_STATUS = 0)

  ....

  the default valor @useddrives is null and null + 'xxxxx' is null

• paulhumphris

  Hall of Fame

  Points: 3496

  More actions

  January 21, 2003 at 2:28 am

  #446825

  You could try this.....

  declare @useddrives varchar(20)

  set @useddrives = ''

  select distinct @useddrives = @useddrives + left(filename, 1) + ';'

  from sysfiles

  if right(@useddrives, 1) = ';'

  set @useddrives = left(@useddrives, len(@useddrives) - 1)

  select @useddrives

  I think it is simpler than a cursor.