Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Concatenation Help Expand / Collapse
Author
Message
Posted Friday, June 6, 2014 7:16 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:31 AM
Points: 89, Visits: 298
Hi,

i am struggling with concatenating integer values.

Declare @id1 int,@id2 int,@id3 int;

sample outputs:

set @id1 = 60;
set @id = 10
set @id3 = 20;

output: 60,10,20

set @id1 = 60;
set @id3 = 20;

output: 60,20

set @id1 = 60;
set @id2 = 10;

output: 60,10

my query:

	select  cast(@id1 as varchar(10)) + ',' + cast(@id2 as varchar(10)) + ',' +
cast(@id3 as varchar(10))

the concept is i need to send the variable values if it has any value

any sample query please. i tried to use casting the variable to convert as string and using ',' to make it as comma separated. but some times it gives the value as 60,0,0. i don't want to send the 0
Post #1578553
Posted Friday, June 6, 2014 7:29 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:31 AM
Points: 89, Visits: 298
this is my next try,



set @id1 = 60
select coalesce(cast(@id1 as varchar(10)),'') + ',' + coalesce(cast(@id1 as varchar(10)),'') + ',' +
coalesce(cast(@id1 as varchar(10)),'')

it gives the result as 60,,

I don't want the ,,. because it it has value result with comma. if it doesn't, no need to append comma

Any help please
Post #1578554
Posted Friday, June 6, 2014 7:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:31 AM
Points: 89, Visits: 298
Some time mind doesn't working to get easy logic

Here is the way i achieved.

select 
stuff
(
coalesce(', ' + cast(@id1 as varchar(10)), '') +
coalesce(', ' + cast(@id2 as varchar(10)), '') +
coalesce(', ' + cast(@id3 as varchar(10)), '') , 1, 2, ''
) as result

Post #1578555
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse