SQL - Format List with Commas & And

USE [######]

GO

/****** Object:  UserDefinedFunction [dbo].[format_list]    Script Date: 05/17/2012 09:39:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===========================================================================================

-- Author:        jefz

-- Create date: 5/17/2012

-- Description:   Use to turn a list that looks like this:

--                     

--                            Bob| Joe| Sally| Billy

--                     

--                      Into a list that looks like this:

--

--                            Bob, Joe, Sally and Billy

--

--                      A '|' is used to delimit the original list incase any of the names (or any

--                      string value used) includes a comma.  When the original list is created, it

--                      should be created with a '|'

-- ===========================================================================================

CREATE FUNCTION [dbo].[format_list]

(

      @list as nvarchar(max)

)

RETURNS nvarchar(max)

AS

BEGIN

      IF len(@list)-len(replace(@list, '|', '')) > 0

                 

                        BEGIN

                              select @list = reverse(@list)

                              select @list = left(@list, CHARINDEX ('|' ,@list)-1) + 'dna ' + replace(right(@list, len(@list)-CHARINDEX ('|' ,@list)), '|', ' ,')

                              select @list = reverse(@list)

                        END

                 

                  ELSE

                 

                        BEGIN

                              select @list = @list

                        END

     

                 

          RETURN @list

END