Prepend Value to Delimited List

  • Chad Casady

    Valued Member

    Points: 62

    Comments posted to this topic are about the item Prepend Value to Delimited List

  • paul_ramster

    Ten Centuries

    Points: 1382

    In general, functions in SQL Server are a feature worth avoiding.

    http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/Are-all-Scalar-User-Defined-Functions-UDF-always-bad.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+sqlquiz+%28SQL+Server+Quiz%29.

    The functions you suggest are probably the "best" or maybe "least-worst", in that they are scalar functions that access no database tables. They do require a small overhead over writing out the function inline, but on the other hand, they promote code-reuse and make the code easier to read.

    However, the contents of your function

    BEGIN

    ????-- If the delimiter passed was null, use an empty string.

    ????set @Delim = IsNull(@Delim, '')

    ????-- If the list is empty, set it equal to the value that was passed in.

    ????if (@ValueList IS NULL)

    ????begin

    ????????set @ValueList = @Item

    ????end

    ????-- If the item passed in was not null, append it to the list.

    ????else if (@Item IS NOT NULL)

    ????begin

    ????????set @ValueList = @Item + @Delim + @ValueList

    ????end

    ????RETURN @ValueList

    END

    can be re-written as

    select @Item + ISNULL(IsNull(@Delim,'') + @ValueList,'')

    and for a simple piece of code like this, I would prefer to write out inline and avoid the overhead of calling a function.

  • Mitesh Oswal

    SSCrazy

    Points: 2798

    DECLARE @STR varchar(100) = 'FN,SP,TBL,091.889'

    DECLARE @Separator VARCHAR(10) = ','

    DECLARE @ValueAdd VARCHAR(10) = '80990'

    DECLARE @Action VARCHAR(10) = 'R'--1. P..PostFix 2. R..Prefix

    SELECT @STR =

    STUFF((

    SELECT

    CASE WHEN @Action = 'R' THEN ISNULL(@Separator+@ValueAdd,'') ELSE '' END

    +

    ISNULL(@Separator+@Str,'')

    +

    CASE WHEN @Action = 'P' THEN ISNULL(@Separator+@ValueAdd,'') ELSE '' END

    ),1,1,'')

    select @STR

    Regards,
    Mitesh OSwal
    +918698619998

  • Mitesh Oswal

    SSCrazy

    Points: 2798

    DECLARE @Numbertable table

    (

    ID INT PRIMARY KEY

    )

    INSERT INTO @Numbertable

    select TOP 1000 ROW_NUMBER() OVER(order by si.object_id)

    from sys.objects si,sys.objects s

    DECLARE @separatolog TABLE

    (

    SeparatorLogID INT,

    ID INT IDENTITY(1,1) PRIMARY KEY

    )

    DECLARE @STR varchar(100) = 'FN,SP,TBL,091.8891'

    DECLARE @Separator VARCHAR(10) = ','

    DECLARE @ValueAdd VARCHAR(10) = '80990'

    DECLARE @Action VARCHAR(10) = '3'--1. P..PostFix 2. R..Prefix 3.Number for postion.

    IF (ISNUMERIC(@Action) =0)

    BEGIN

    SELECT @STR =

    STUFF((

    SELECT

    CASE WHEN @Action = 'R' THEN ISNULL(@Separator+@ValueAdd,'') ELSE '' END

    +

    ISNULL(@Separator+@Str,'')

    +

    CASE WHEN @Action = 'P' THEN ISNULL(@Separator+@ValueAdd,'') ELSE '' END

    ),1,1,'')

    END

    ELSE

    BEGIN

    INSERT INTO @separatolog

    select distinct CHARINDEX(',',@str,ID)

    from @Numbertable

    where ID <= LEN(@str) and CHARINDEX(',',@str,ID) > 0

    ORDER BY 1

    DECLARE @CurrentSepartorID INT

    SET @CurrentSepartorID = isnull((select SeparatorLogID

    from @separatolog where ID = CAST(@Action AS INT)-1),CASE WHEN @Action = 1 THEN 0 ELSE LEN(@str)+1 END)

    select @STR = STUFF(SUBSTRING(ISNULL(@Separator+@str,''),1,@CurrentSepartorID)

    +ISNULL(@Separator+@ValueAdd,'')

    +ISNULL(@Separator+NULLIF(SUBSTRING(@str,@CurrentSepartorID+1,LEN(@str)),''),''),1,1,'')

    END

    select @STR

    Regards,
    Mitesh OSwal
    +918698619998

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script. Nice continuation on the earlier one.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply