Notes from the field on ColdFusion (or related) technical issues.

Monday, January 26, 2009

ListFirst, ListRest in SQL Server

So, this has probably been done a million times before, but I needed ColdFusion-style ListFirst and ListRest functions for creating a stored procedure in a database that violates First Normal Form. <sigh/>

Note that the behavior varies slightly from CF. The delimiter is forced to be a comma, and multiple consecutive delimiters are treated as multiple elements. (CF eating 3 commas at a time always annoyed me.)

So, here's the DDL for them:


CREATE FUNCTION [dbo].[listFirst] (@list nvarchar(4000))
RETURNS nvarchar(4000)
AS BEGIN
DECLARE @pos int
DECLARE @ret nvarchar(4000)
SET @pos = charindex(',', @list)
IF @pos > 0
SET @ret = left(@list, @pos-1)
ELSE
set @ret = @list
RETURN @ret
END


CREATE FUNCTION [dbo].[listRest] (@list nvarchar(4000))
RETURNS nvarchar(4000)
AS BEGIN
DECLARE @pos int
DECLARE @ret nvarchar(4000)
SET @pos = charindex(',', @list)
IF @pos > 0
SET @ret = substring(@list, @pos+1, len(@list)-@pos)
ELSE
SET @ret = ''
RETURN @ret
END


As a side note, it just occurred to me that you can probably work around the multiple consecutive delimiter thing by adding whitespace, then trimming:


<cfloop list="#replace(myList, ",", ", ", "ALL")#" index="item">
<cfset item=trim(item) />
...
</cfloop>


Why didn't I think of that years ago?

1 comment:

  1. I recently came across your blog and have been reading along. I thought I would leave my first comment.

    I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.

    -
    ColdFusion Web Development

    ReplyDelete