CODEWITHSUNDEEP
sql-servert-sqlconcatenation
MFB
MFB

Reputation: 19817

T-SQL concatenate VarChar with comma

I'm new to SQL. I have this working SQL function, but I would like to join the results with a comma in a way that leaves no extraneous commas if the result is empty. I had a play with COALESCE but I couldn't get it to work. Any clues or help would be great.

In Python I would do this:

'abcdef'.join(', ')

Here is the SQL function now:

USE [Broadcast]
GO
/****** Object:  UserDefinedFunction [dbo].[MapConsumerAdvice]    Script Date: 04/24/2013 14:49:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[MapConsumerAdvice] 
(
    @A as Int,
    @B as Int,
    @C as Int,
    @D as Int,
    @E as Int,
    @F as Int,
    @G as Int,
    @H as Int
)
RETURNS VarChar(20)
AS
BEGIN

    DECLARE @Result as VarChar(999)

    SET @Result = ''

    IF (@A > 0) SET @Result = @Result + ',' + 'a'
    IF (@B > 0) SET @Result = @Result + ',' + 'd'
    IF (@C > 0) SET @Result = @Result + ',' + 'h'
    IF (@D > 0) SET @Result = @Result + ',' + 'l'
    IF (@E > 0) SET @Result = @Result + ',' + 'm'
    IF (@F > 0) SET @Result = @Result + ',' + 'n'
    IF (@G > 0) SET @Result = @Result + ',' + 's'
    IF (@H > 0) SET @Result = @Result + ',' + 'v'

    Return @Result

END

Upvotes: 2

Views: 352

Answers (3)

Nishad
Nishad

Reputation: 426

add below code at end of function,

RETURN REPLACE(@result,',,',',')

Upvotes: 0

ps2goat
ps2goat

Reputation: 8485

You do need to check if anything was assigned.

USE [Broadcast]
GO
/****** Object:  UserDefinedFunction [dbo].[MapConsumerAdvice]    Script Date: 04/24/2013 14:49:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[MapConsumerAdvice] 
(
    @A as Int,
    @B as Int,
    @C as Int,
    @D as Int,
    @E as Int,
    @F as Int,
    @G as Int,
    @H as Int
)
RETURNS VarChar(20)
AS
BEGIN

DECLARE @Result as VarChar(999)

SET @Result = ''

IF (@A > 0) SET @Result = @Result + ',' + 'a'
IF (@B > 0) SET @Result = @Result + ',' + 'd'
IF (@C > 0) SET @Result = @Result + ',' + 'h'
IF (@D > 0) SET @Result = @Result + ',' + 'l'
IF (@E > 0) SET @Result = @Result + ',' + 'm'
IF (@F > 0) SET @Result = @Result + ',' + 'n'
IF (@G > 0) SET @Result = @Result + ',' + 's'
IF (@H > 0) SET @Result = @Result + ',' + 'v'

IF PATINDEX(',%', @Result) > 1 Set @Result = SUBSTRING(@Result, 2, LEN(@Result));

Return @Result

END

Upvotes: 0

TechDo
TechDo

Reputation: 18649

Try

RETURN SUBSTRING(@Result, 2, LEN(@Result))

or

RETURN RIGHT(@Result, LEN(@Result)-1)

instead of Return @Result

Upvotes: 1

Related Questions