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
10.3.0.98;10.3.0.15;10.3.0
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 <> ''
You can achieve this in a procedure of function using the approach below:
DECLARE @concat varchar(100)
SELECT @concat = COALESCE(@concat + '; ', '') +
REPLACE(REPLACE(REPLACE(Pa ram, 'IP_ADDRESS=0', ''),'.030.000.0','.3.0.'), '.030.000. 1','.3.0.1 ')
FROM dbo.tb_Peripherals
WHERE Param <> ''
SELECT @concat
DECLARE @concat varchar(100)
SELECT @concat = COALESCE(@concat + '; ', '') +
REPLACE(REPLACE(REPLACE(Pa
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 ?
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.
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.
ASKER
Hey rrjegan17, will that code work in VBS?
Does not seem to be working so far;
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
Have you tried mine yet ? It is worth checking... Will add in the other stuff while the rugby is on...
ASKER
HI mark_wills I tried yours in my VBS code and got the following error;
[error attached]
vbs-error-salto-controller-scrip.JPG
[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(Pa ram, 'IP_ADDRESS=0', ''),'.030.000.0','.3.0.'), '.030.000. 1','.3.0.1 ') as param1
, substring((select ';'+convert(varchar,ip_add ress) from tb_peripherals where param <> '' for xml path('')),2,8000) as str_list
from tb_peripherals where param <> ''
Cheers,
try this instead :
select REPLACE(REPLACE(REPLACE(Pa
, substring((select ';'+convert(varchar,ip_add
from tb_peripherals where param <> ''
Cheers,
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Its OK I fixed it.
Thank you for your QUICK and accurate responses keep up the GREAT work.
Enjoy the Rugby
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 !
select substring((select ';'+convert(varchar,ip_add