SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Chad Casady / Prepend Value to Delimited List / Latest Posts

RE: Prepend Value to Delimited List

Mitesh Oswal — Wed, 27 Apr 2011 03:32:32 GMT

[code="sql"]DECLARE @Numbertable table( ID INT PRIMARY KEY )INSERT INTO @Numbertableselect TOP 1000 ROW_NUMBER() OVER(order by si.object_id)from sys.objects si,sys.objects sDECLARE @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) BEGINSELECT @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,'')ENDELSEBEGININSERT INTO @separatologselect distinct CHARINDEX(',',@str,ID)from @Numbertablewhere ID <= LEN(@str) and CHARINDEX(',',@str,ID) > 0ORDER BY 1DECLARE @CurrentSepartorID INT SET @CurrentSepartorID = isnull((select SeparatorLogIDfrom @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,'')ENDselect @Str [/code]

RE: Prepend Value to Delimited List

Mitesh Oswal — Wed, 27 Apr 2011 03:03:31 GMT

[code="sql"]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..PrefixSELECT @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[/code]

RE: Prepend Value to Delimited List

paul_ramster — Wed, 09 Mar 2011 02:38:52 GMT

In general, functions in SQL Server are a feature worth avoiding.[url]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[/url].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 [code="sql"]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 @ValueListEND[/code]can be re-written as[code="sql"]select @Item + ISNULL(IsNull(@Delim,'') + @ValueList,'')[/code]and for a simple piece of code like this, I would prefer to write out inline and avoid the overhead of calling a function.

Prepend Value to Delimited List

Chad Casady — Tue, 08 Mar 2011 21:08:59 GMT

Comments posted to this topic are about the item [B]Prepend Value to Delimited List[/B]