Common Sql Clr Functions - GROUP_CONCAT with a delimiter, and Last occurrence String Replacement

GROUP_CONCAT.cs

// Minor modification of Microsoft's Concatenate function here http://msdn.microsoft.com/en-us/library/ms131056.aspx, 
// by  allowing for a different delimiter character. Note that default values aren't supported by SQLCLR Functions or Aggregates

using System;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = false,
    IsInvariantToOrder = false,
    MaxByteSize = 8000)
]
public class Group_Concat : IBinarySerialize
{
    private StringBuilder _intermediateResult;

    public void Init()
    {
        _intermediateResult = new StringBuilder();
    }

    public void Accumulate(SqlString value, SqlString delimiter)
    {
        if (value.IsNull)
        {
            return;
        }

        _intermediateResult.Append(value.Value)
            .Append(delimiter);
    }

    public void Merge(Group_Concat other)
    {
        _intermediateResult.Append(other._intermediateResult);
    }

    public SqlString Terminate()
    {
        var output = string.Empty;
        if (_intermediateResult != null
            && _intermediateResult.Length > 0)
        {
            output = _intermediateResult.ToString(0, _intermediateResult.Length - 1);
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        _intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(_intermediateResult.ToString());
    }
}

RegisterClrAssembly.sql

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

CREATE ASSEMBLY ClrFunctions FROM 'C:\Temp\ClrFunctions.dll';
GO

CREATE AGGREGATE dbo.GROUP_CONCAT(@input nvarchar(MAX), @delimiter nvarchar(MAX)) RETURNS nvarchar(max)
EXTERNAL NAME [ClrFunctions].[Group_Concat];
GO

CREATE FUNCTION dbo.ReplaceLast(@source nvarchar(MAX), @find nvarchar(MAX), @replace nvarchar(MAX), @countFromRight INT) RETURNS nvarchar(max)
EXTERNAL NAME [ClrFunctions].UserDefinedFunctions.ReplaceLast;
GO

ReplaceLast.cs

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Linq;

public partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlString ReplaceLast(SqlString source, SqlString find, SqlString replace, int countFromRight)
    {
        var splits = source.Value
            .Split(new[] {find.Value}, StringSplitOptions.None);

        var replacePoint = splits.Length - countFromRight;

        return (replacePoint > 0)
            ? new SqlString(
                string.Join(replace.Value,
                    new[] { string.Join(find.Value, splits.Take(replacePoint)) }
                    .Union(splits.Skip(replacePoint))))
            : source;
   }
}

SampleUsage.sql

--Example Data swiped from here http://msdn.microsoft.com/en-us/library/ms131056.aspx

CREATE TABLE BookAuthors
(
   BookID   int       NOT NULL,
   AuthorName    nvarchar(200) NOT NULL
);

INSERT BookAuthors VALUES
(1, 'Johnson'),
(2, 'Taylor'),
(3, 'Steven'),
(2, 'Mayler'),
(3, 'Roberts'),
(3, 'Michaels');

SELECT dbo.GROUP_CONCAT(AuthorName, ',')
FROM BookAuthors;

SELECT dbo.ReplaceLast('John,Jimmy,Mary', ',', ' and ', 1);

SELECT dbo.ReplaceLast(dbo.GROUP_CONCAT(AuthorName, ','), ',', ' and ', 1)
FROM BookAuthors;

Although SqlClr isn't always an available option (e.g. on Azure), it is an important feature to avoid the atrocious workarounds such as the infamous STUFF / FOR XML PATH hack and other nasties which attempt to compensate for the poor String manipulation capabilities in SqlServer.

This Gist is as a result of answering this SO question here - my conscience got the better of me to provide a more maintainable, readable approach to GROUP_CONCAT and replacing the last occurrence of a comma in a string.