Crear una función SqlClr. Split

Existen muchas alternativas a la separación de strings en TSQL, si bien la más rápida puede ser crear una función SQLCLR. Pero esto implica disponer de amplios conocimientos de los entresijos de SQL o disponer de Visual Studio.

Si dispones de Visual Studio, por ejemplo en su versión Community, que es totalmente gratuita, puedes crear con suma facilidad codigo SqlClr para que te apoye en tús posteriores desarrollos.

Para este laboratorio, voy a realizar el mismo, con una edición de VS 2019 Enterprise además de un servidor SQL Server 2019 Developer Edition.

Lo primero crear un proyecto Sql Database

Siguiente

Una vez escogido el nombre deseado, y la carpeta. Create

Desde el Solution Explorer

Desde Sql Management Studio, me he creado una base de datos nueva.

En la nueva ventana, Select Connection

Una vez hemos comprobado la conexión a la base de datos. Connect

Y en la ventana a la que volvemos. Start

El resumen nos muestra lo que ha preparado y ya en el explorador de soluciones, vemos las nuevas carpetas. Finish

Sobre el explorador de Soluciones, en el proyecto Add > new Item

Ahora ya tenemos nuestra clase clr, para poder anexar nuestro código.

Para el ejemplo yo voy a utilizar la siguiente función clr.

http://dataeducation.com/sqlclr-string-splitting-part-2-even-faster-even-more-scalable/

/* Code 1.0 */

    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    public partial class UserDefinedFunctions
    {
        [Microsoft.SqlServer.Server.SqlFunction(
           FillRowMethodName = "FillRow_Multi",
           TableDefinition = "item nvarchar(4000)"
           )
        ]
        public static IEnumerator SplitString_Multi(
          [SqlFacet(MaxSize = -1)]
          SqlChars Input,
          [SqlFacet(MaxSize = 255)]
          SqlChars Delimiter
           )
        {
            return (
                (Input.IsNull || Delimiter.IsNull) ?
                new SplitStringMulti(new char[0], new char[0]) :
                new SplitStringMulti(Input.Value, Delimiter.Value));
        }
        public static void FillRow_Multi(object obj, out SqlString item)
        {
            item = new SqlString((string)obj);
        }
        public class SplitStringMulti : IEnumerator
        {
            public SplitStringMulti(char[] TheString, char[] Delimiter)
            {
                theString = TheString;
                stringLen = TheString.Length;
                delimiter = Delimiter;
                delimiterLen = (byte)(Delimiter.Length);
                isSingleCharDelim = (delimiterLen == 1);
                lastPos = 0;
                nextPos = delimiterLen * -1;
            }
            #region IEnumerator Members
            public object Current
            {
                get
                {
                    return new string(theString, lastPos, nextPos - lastPos);
                }
            }
            public bool MoveNext()
            {
                if (nextPos >= stringLen)
                    return false;
                else
                {
                    lastPos = nextPos + delimiterLen;
                    for (int i = lastPos; i < stringLen; i++)
                    {
                        bool matches = true;
                        //Optimize for single-character delimiters
                        if (isSingleCharDelim)
                        {
                            if (theString[i] != delimiter[0])
                                matches = false;
                        }
                        else
                        {
                            for (byte j = 0; j < delimiterLen; j++)
                            {
                                if (((i + j) >= stringLen) || (theString[i + j] != delimiter[j]))
                                {
                                    matches = false;
                                    break;
                                }
                            }
                        }
                        if (matches)
                        {
                            nextPos = i;
                            //Deal with consecutive delimiters
                            if ((nextPos - lastPos) > 0)
                                return true;
                            else
                            {
                                i += (delimiterLen-1);
                                lastPos += delimiterLen;
                            }
                        }
                    }
                    lastPos = nextPos + delimiterLen;
                    nextPos = stringLen;
                    if ((nextPos - lastPos) > 0)
                        return true;
                    else
                        return false;
                }
            }
            public void Reset()
            {
                lastPos = 0;
                nextPos = delimiterLen * -1;
            }
            #endregion
            private int lastPos;
            private int nextPos;
            private readonly char[] theString;
            private readonly char[] delimiter;
            private readonly int stringLen;
            private readonly byte delimiterLen;
            private readonly bool isSingleCharDelim;
        }
    };

Agrego el código, y botón derecho sobre el proyecto. Publish

Pulso sobre Edit y escojo la conexión anterior.

Publish

Veo que me genera un error (esperado).

Leo el mensaje

Creating Assembly [Db_ClrFunctions]…
(93,1): SQL72014: .Net SqlClient Data Provider: Msg 10343, Level 14, State 1, Line 1 Error de CREATE o ALTER ASSEMBLY en el ensamblado «Db_ClrFunctions» con la opción SAFE o EXTERNAL_ACCESS porque la opción «clr strict security» de sp_configure está establecida en 1. Microsoft recomienda que firme el ensamblado con un certificado o clave asimétrica que tengan un inicio de sesión correspondiente con el permiso UNSAFE ASSEMBLY. También puede confiar en el ensamblado mediante sp_add_trusted_assembly.

Me voy a Management Studio, y cambio la opción de clr strict security

-- Code 1.1

EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;

GO

Vuelvo a publicar.

Verifico la misma

Compruebo el funcionamiento correcto de la función split.