SQL Server - Função table-valued para quebrar uma string em linhas com tamanho de até N caracteres

Post Views 1,971 views

Esse post é a parte 2 de 5 da série String Split

SQL Server – Quebrando strings em sub-strings utilizando separador (Split string)
SQL Server – Função table-valued para quebrar uma string em linhas com tamanho de até N caracteres
Como quebrar um string em uma tabela de substrings utilizando um delimitador no SQL Server
SQL Server – charindexada: Uma função diferente para quebrar strings delimitadas (split)
SQL Server – Utilizando a STRING_SPLIT para transformar strings de uma linha em colunas

Reading time 3 minutes

Olá pessoal,
Tudo bem com vocês ?

Neste post de hoje, vou compartilhar com vocês uma função UDF do tipo Table-valued que permite quebrar strings em linhas, forçando que o tamanho máximo de cada linha seja N caracteres separados por um caractere separador definido na chamada da função.

Essa função surgiu de uma necessidade em um projeto crítico onde trabalho, no qual temos strings VARCHAR(MAX) e precisamos exportar essas strings em um arquivo TXT com tamanho máximo de 60 caracteres, mantendo um Id para identificar o registro original e um ranking (usei ROW_NUMBER) para identificar a ordem de cada parte da string.

Interessado em aprender mais sobre split?

Exemplos de uso

Exemplo simples:

Exemplo com CROSS APPLY:

-- Utilizando o caractere espaço (' ') como separador e tamanho máximo da string
-- limitado a 10 caracteres

IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste
CREATE TABLE #Teste (
    Id_Texto INT IDENTITY(1, 1),
    Ds_Texto VARCHAR(MAX)
)

INSERT INTO #Teste
VALUES('Dirceu Resende - Testando a função do CLR numa table valued function'), 
('No caso de uma palavra ficar maior que a quantidade de caracteres estipulada, a string não será "quebrada". Exemplo: https://dirceuresende.com'),
('Para saber mais sobre CLR, acesse o meu blog')

SELECT 
    *
FROM 
    #Teste A
    CROSS APPLY CLR.dbo.fncSplit_Texto(A.Ds_Texto, ' ', 10) B

-- Utilizando o caractere espaço (' ') como separador e tamanho máximo da string

-- limitado a 10 caracteres

IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste

CREATE TABLE #Teste (

Id_Texto INT IDENTITY(1, 1),

Ds_Texto VARCHAR(MAX)

)

INSERT INTO #Teste

VALUES('Dirceu Resende - Testando a função do CLR numa table valued function'),

('No caso de uma palavra ficar maior que a quantidade de caracteres estipulada, a string não será "quebrada". Exemplo: https://dirceuresende.com'),

('Para saber mais sobre CLR, acesse o meu blog')

SELECT

FROM

#Teste A

CROSS APPLY CLR.dbo.fncSplit_Texto(A.Ds_Texto, ' ', 10) B

Result:

Código-fonte da função

Para utilizar a função demonstrada acima, basta criar a função CLR do tipo table-valued na sua instância. Para entender melhor o que é o CLR e como criar a sua primeira biblioteca CLR, veja mais no post Introdução ao SQL CLR (Common Language Runtime) no SQL Server.

using System;
using System.Collections;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{

    private class SplitTexto
    {

        public SqlInt32 Id;
        public SqlString Ds_Palavra;

        public SplitTexto(SqlInt32 id, SqlString dsPalavra)
        {
            Id = id;
            Ds_Palavra = dsPalavra.IsNull ? "" : dsPalavra;
        }
    }

    [Microsoft.SqlServer.Server.SqlFunction(
            FillRowMethodName = "FillSplitTexto",
            TableDefinition = "Id INT, Ds_Palavra NVARCHAR(4000)"
        )]
    public static IEnumerable fncSplit_Texto(string Ds_Texto, string Ds_Separador, int Tamanho_Palavra)
    {

        var splitTextoCollection = new ArrayList();

        if (string.IsNullOrEmpty(Ds_Texto))
            return splitTextoCollection;

        var contador = 1;
        var palavra = "";
        Ds_Texto = Ds_Texto + Ds_Separador;

        while (Ds_Texto.Length > 0)
        {

            var substring = Ds_Texto.Substring(0, Ds_Texto.IndexOf(Ds_Separador, StringComparison.Ordinal)).Trim();

            if (palavra == " ")
                palavra = "";

            if ((palavra + " " + substring).Length > Tamanho_Palavra)
            {

                splitTextoCollection.Add(new SplitTexto(
                    contador,
                    palavra.Trim()
                ));

                palavra = substring;
                contador++;

            }
            else
            {
                palavra = palavra == " " ? substring : (palavra + " " + substring);
            }

            Ds_Texto = Ds_Texto.Substring(Ds_Texto.IndexOf(Ds_Separador, StringComparison.Ordinal) + 1);

        }


        if (palavra.Trim().Length > 0)
        {

            splitTextoCollection.Add(new SplitTexto(
                contador,
                palavra.Trim()
            ));

        }


        return splitTextoCollection;

    }

    protected static void FillSplitTexto(object objSplitTexto, out SqlInt32 id, out SqlString dsPalavra)
    {

        var splitTexto = (SplitTexto) objSplitTexto;

        id = splitTexto.Id;
        dsPalavra = splitTexto.Ds_Palavra.IsNull ? "" : splitTexto.Ds_Palavra;

    }

};

using System;

using System.Collections;

using System.Data.SqlTypes;

public partial class UserDefinedFunctions

{

private class SplitTexto

{

public SqlInt32 Id;

public SqlString Ds_Palavra;

public SplitTexto(SqlInt32 id, SqlString dsPalavra)

{

Id = id;

Ds_Palavra = dsPalavra.IsNull ? "" : dsPalavra;

}

[Microsoft.SqlServer.Server.SqlFunction(

FillRowMethodName = "FillSplitTexto",

TableDefinition = "Id INT, Ds_Palavra NVARCHAR(4000)"

)]

public static IEnumerable fncSplit_Texto(string Ds_Texto, string Ds_Separador, int Tamanho_Palavra)

{

var splitTextoCollection = new ArrayList();

if (string.IsNullOrEmpty(Ds_Texto))

return splitTextoCollection;

var contador = 1;

var palavra = "";

Ds_Texto = Ds_Texto + Ds_Separador;

while (Ds_Texto.Length > 0)

{

var substring = Ds_Texto.Substring(0, Ds_Texto.IndexOf(Ds_Separador, StringComparison.Ordinal)).Trim();

if (palavra == " ")

palavra = "";

if ((palavra + " " + substring).Length > Tamanho_Palavra)

{

splitTextoCollection.Add(new SplitTexto(

contador,

palavra.Trim()

));

palavra = substring;

contador++;

}

else

{

palavra = palavra == " " ? substring : (palavra + " " + substring);

}

Ds_Texto = Ds_Texto.Substring(Ds_Texto.IndexOf(Ds_Separador, StringComparison.Ordinal) + 1);

}

if (palavra.Trim().Length > 0)

{

splitTextoCollection.Add(new SplitTexto(

contador,

palavra.Trim()

));

}

return splitTextoCollection;

}

protected static void FillSplitTexto(object objSplitTexto, out SqlInt32 id, out SqlString dsPalavra)

{

var splitTexto = (SplitTexto) objSplitTexto;

id = splitTexto.Id;

dsPalavra = splitTexto.Ds_Palavra.IsNull ? "" : splitTexto.Ds_Palavra;

}

};

And that's it, folks!
Um abraço e até a próxima.

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Função table-valued para quebrar uma string em linhas com tamanho de até N caracteres

Exemplos de uso

Código-fonte da função

You may also like...

Deixe uma respostaCancelar resposta

Exemplos de uso

Código-fonte da função

You may also like...

SQL Server – Operações com arquivos utilizando xp_cmdshell (Como listar, ler, escrever, copiar, excluir e mover arquivos)

SQL Server – String or binary data would be truncated: O que é, como identificar a causa raiz e como corrigir

[Vídeo] – Introdução ao SQLCLR

Deixe uma respostaCancelar resposta