SQL Function to transform rows into delimited text

CREATE FUNCTION CitiesAsDelimitedString
(
@Delimiter AS VARCHAR(5)
)
RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @str AS VARCHAR(8000)
SET @str = ''

SELECT @str=@str+CASE @str WHEN '' THEN CityName ELSE @Delimiter + CityName END
FROM Cities

END
---------------------------------------------------------

Usage:
select dbo.CitiesAsDelimitedString(', ')

SQL Function to parse a delimited string

CREATE FUNCTION ParseDelimitedString
(
@str AS VARCHAR (8000),
@delimiter AS VARCHAR (5)
)
RETURNS @RETTABLE TABLE(string VARCHAR(50))

AS

BEGIN
WHILE LEN(@STR) > 0
BEGIN
IF CHARINDEX(@DELIMITER, @STR) = 0
BEGIN
INSERT INTO @RETTABLE VALUES (LTRIM(RTRIM(@STR)))
SET @STR = ''
END
ELSE
BEGIN
INSERT INTO @RETTABLE
VALUES (SUBSTRING(@STR, 1, CHARINDEX(@DELIMITER, @STR)-1))
SET @STR = RIGHT(@STR, LEN(@STR) - CHARINDEX(@DELIMITER, @STR) - 1)
END
END

RETURN
END
--------------------------------------------------------------------------------------------------------------------------
Usage:
select * from dbo.ParseDelimitedString('cat, dog, frog, pig', ', ')