Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Concatenate Sql Rowset Results

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

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

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

1 Solution
Mark WillsTopic AdvisorCommented:
try :

select  substring((select ';'+convert(varchar,ip_address) from tb_peripherals where param <> '' for xml path('')),2,8000)
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

Mark WillsTopic AdvisorCommented:
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 ?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

SharathData EngineerCommented:
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.
CurtinPropAuthor Commented:
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)
  Set oComm = Nothing
  Set oConn = Nothing

Open in new window

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

[error attached]
Mark WillsTopic AdvisorCommented:
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 <> ''

CurtinPropAuthor Commented:
Hi Mark,

I got the follow record set back;
Mark WillsTopic AdvisorCommented:
Now I get it... You only want the one row with the PARAM1 entries concatenated !  And there are a few NULL's in there (the replace does that), so taken care of that too...

One more for the road ?

select top 1 substring((select ';'+convert(varchar,REPLACE(REPLACE(REPLACE(Param, 'IP_ADDRESS=0', ''),'.030.000.0','.3.0.'),'.030.000.1','.3.0.1')) from tbl_peripherals where param <> '' for xml path('')),2,8000) as PARAM1
from tbl_peripherals where param <> ''
CurtinPropAuthor Commented:
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;
CurtinPropAuthor Commented:
Its OK I fixed it.

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

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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now