Link to home
Start Free TrialLog in
Avatar of CurtinProp
CurtinProp

asked on

Concatenate Sql Rowset Results

I need to get the results from this query into a concatenated string like so;

10.3.0.98;10.3.0.15;10.3.0.16;10.3.0.17

If anyone has suggestions on how to improve my dodgy nested replace code that would be a bonus.

Cheers
SELECT
REPLACE(REPLACE(REPLACE(Param, 'IP_ADDRESS=0', ''),'.030.000.0','.3.0.'),'.030.000.1','.3.0.1') AS PARAM1
 FROM dbo.tb_Peripherals WHERE Param <> ''

Open in new window

Avatar of Mark Wills
Mark Wills
Flag of Australia image

try :

select  substring((select ';'+convert(varchar,ip_address) from tb_peripherals where param <> '' for xml path('')),2,8000)
You can achieve this in a procedure of function using the approach below:

DECLARE @concat varchar(100)

SELECT @concat = COALESCE(@concat + '; ', '') +
REPLACE(REPLACE(REPLACE(Param, 'IP_ADDRESS=0', ''),'.030.000.0','.3.0.'),'.030.000.1','.3.0.1')
FROM dbo.tb_Peripherals
WHERE Param <> ''

SELECT @concat

Didn't even look at the replace... looks more like a case statement...

case param when 'ip_address=0' then ''
                   when '030.000.0' then '.3.0.'
                   when '030.000.1' then '.3.0.1'
                   else param
                   end as new_param

which is not entirely correct, I know, but can look at that - is there any sample data ? a small spreadsheet maybe ?
In rrjegan17 post, it would be good to declare the variable as
DECLARE @concat varchar(max)  or
DECLARE @concat varchar(4000)   -- based on your SQL version
 
you won't lose any data after concatenating if it exceeds 100.
 
Avatar of CurtinProp
CurtinProp

ASKER

Hey rrjegan17, will that code work in VBS?

Does not seem to be working so far;

  Dim oConn, oComm
  set oConn= createobject("ADODB.Connection")
  Set oComm = createObject("ADODB.COMMAND")
 
  oConn.Open ("DSN=salto;UID=salto;PWD=Properties2008")
  oComm.ActiveConnection = oConn
 
  oComm.CommandText = "DECLARE @concat varchar(1000) SELECT @concat = COALESCE(@concat + ';', '') +REPLACE(REPLACE(REPLACE(Param, 'IP_ADDRESS=0', ''),'.030.000.0','.3.0.'),'.030.000.1','.3.0.1') FROM dbo.tb_Peripherals WHERE Param <> ''SELECT @concat "
  Set oRecordSet = oComm.Execute
 
  Do Until oRecordSet.EOF
    msgbox oRecordSet.Fields(0).Value & " " & oRecordSet.Fields(1)
    oRecordSet.MoveNext
  Loop  
 
 
  oConn.Close
 
  Set oComm = Nothing
  Set oConn = Nothing

Open in new window

Have you tried mine yet ? It is worth checking... Will add in the other stuff while the rugby is on...
HI mark_wills I tried yours in my VBS code and got the following error;


[error attached]
vbs-error-salto-controller-scrip.JPG
Yep, was thinking you might have tried it interactively first...

try this instead :


select  REPLACE(REPLACE(REPLACE(Param, 'IP_ADDRESS=0', ''),'.030.000.0','.3.0.'),'.030.000.1','.3.0.1') as param1
, substring((select ';'+convert(varchar,ip_address) from tb_peripherals where param <> '' for xml path('')),2,8000) as str_list
from tb_peripherals where param <> ''


Cheers,
Hi Mark,

I got the follow record set back;
vbs-error-salto-controller-scrip.JPG
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect Mark, works great in my SSMS but it isn't work in my VBS code which I listed above.

I'm getting the same Error box which I also posted above too. I'm not sure if you could assist but I found the following page and tried all in it;
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
Its OK I fixed it.

Thank you for your QUICK and accurate responses keep up the GREAT work.

Enjoy the Rugby
Danged column names ! Glad you got it working, tis excellent news, and thanks very much... all with 10 minutes before kick-off - bargain !