Skip to content

Instantly share code, notes, and snippets.

@nonnb
Last active August 29, 2015 14:11
Show Gist options
  • Save nonnb/465a355fda05707c82cc to your computer and use it in GitHub Desktop.
Save nonnb/465a355fda05707c82cc to your computer and use it in GitHub Desktop.
Common Sql Clr Functions - GROUP_CONCAT with a delimiter, and Last occurrence String Replacement
// 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());
}
}
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
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;
}
}
--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;
@nonnb
Copy link
Author

nonnb commented Dec 11, 2014

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment