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
No comments:
Post a Comment