SQL Server – Quebrando strings em sub-strings utilizando separador (Split string)

Visualizações: 11.822
Esse post é a parte 1 de 5 da série String Split
Tempo de Leitura: 3 minutos

Olá Pessoal!
Boa noite!

Hoje eu vou apresentar uma função criada pelo meu amigo Murilo Mielke, que permite quebrar uma string delimitada por algum (ou alguns) caracter em sub-strings. Para quem é desenvolvedor Web, é o que faz a função explode do PHP ou a Split do Java, Javascript, C#, etc..

Basicamente, você tem uma string como o exemplo abaixo:

nome;nascimento;email
Nome 1;1994-05-29;[email protected]
Nome 2;1981-07-10;[email protected]
Nome 3;2001-02-27;[email protected]

Imagine que você queira recuperar apenas o nome e o e-mail dos registros acima. Dividindo cada linha utilizando o caracter “;” como separador, temos uma 3 sub-strings. É exatamente isso que a função abaixo faz:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE FUNCTION [dbo].[fncSplit] ( @String varchar(8000), @Separador varchar(8000), @PosBusca int )
RETURNS varchar(8000)
AS BEGIN
    
    DECLARE @Index int, @Max int, @Retorno varchar(8000)
 
    DECLARE @Partes as TABLE ( Id_Parte int identity(1,1), Texto varchar(8000) )
 
    SET @Index = charIndex(@Separador,@String)
 
    WHILE (@Index > 0) BEGIN    
        INSERT INTO @Partes SELECT SubString(@String,1,@Index-1)
        SET @String = Rtrim(Ltrim(SubString(@String,@Index+Len(@Separador),Len(@String))))
        SET @Index = charIndex(@Separador,@String)
    END
 
    IF (@String != '') INSERT INTO @Partes SELECT @String
 
    SELECT @Max = Count(*) FROM @Partes
 
    IF (@PosBusca = 0) SET @Retorno = Cast(@Max as varchar(5))
    IF (@PosBusca < 0) SET @PosBusca = @Max + 1 + @PosBusca
    IF (@PosBusca > 0) SELECT @Retorno = Texto FROM @Partes WHERE Id_Parte = @PosBusca
 
    RETURN RTRIM(LTRIM(@Retorno))
 
END
GO

Exemplos de uso:

Transact-SQL
1
2
3
4
DECLARE @strOrigem VARCHAR(MAX) = 'Testando|String|Para|O|Blog'
 
SELECT dbo.fncSplit(@strOrigem, '|', 1) -- Vai imprimir na tela 'Testando'
SELECT dbo.fncSplit(@strOrigem, '|', 5) -- Vai imprimir na tela 'Blog'

Utilizando o CLR

Uma outra alternativa para resolver esse problema é utilizar o CLR, recurso que permite criar códigos escritos em .NET (C# ou VB.NET) dentro do SQL Server, onde geralmente possuem um desempenho bem superior aos códigos T-SQL. Caso você não saiba o que é o SQLCLR, saiba mais acessando o post Introdução ao SQL CLR (Common Language Runtime) no SQL Server.

Visualizar código-fonte da função C# (CLR)
C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
using System;
using System.Data.SqlTypes;
 
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fncSplit(SqlString Ds_Palavra, SqlString Ds_Delimitador, SqlInt32 Qt_Indice)
    {
 
        if (Ds_Palavra.IsNull || Qt_Indice.IsNull)
            return SqlString.Null;
 
        if (Ds_Delimitador.IsNull)
            return Ds_Palavra.Value;
 
        var indice = (Qt_Indice.Value == 0) ? 1 : Qt_Indice.Value;
        var palavra = Ds_Palavra.Value.Split(new string[] { Ds_Delimitador.Value }, StringSplitOptions.None);
 
        if (Qt_Indice.Value == 0)
            return palavra.Length.ToString();
 
        if (palavra.Length < Qt_Indice.Value)
            return SqlString.Null;
 
        return indice > 0 ? palavra[indice - 1] : palavra[palavra.Length - Math.Abs(indice)];
 
    }
}

Teste de performance

Enquanto as duas funções nos trazem o mesmo resultado, a forma de execução delas é totalmente diferente. A função T-SQL é mais simples de ser implementada (basta um F5), e a função CLR dá mais trabalho para ser criada no banco, pois será necessário criar um assembly no Visual Studio para utilizá-la (caso você não tenha nenhum. Se você já tem um criado, aí é fácil) além do fato de ser escrita em C#, uma linguagem amplamente conhecida por desenvolvedores, mas nem tanto por DBA’s (Tudo isso já foi falado no post de introdução do SQL CLR).

Dado essas fatos, você deve estar se perguntando: “Então porque utilizar a função CLR? Qual a vantagem?”. E a resposta é essa: PERFORMANCE.

Query utilizada para os testes:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
    CLR.dbo.fncSplit(Descricao, '|', 1),
    CLR.dbo.fncSplit(Descricao, '|', 2),
    CLR.dbo.fncSplit(Descricao, '|', 3),
    CLR.dbo.fncSplit(Descricao, '|', 4)
FROM
    dbo.Teste_Group_Concat
 
 
SELECT
    dbo.fncSplit(Descricao, '|', 1),
    dbo.fncSplit(Descricao, '|', 2),
    dbo.fncSplit(Descricao, '|', 3),
    dbo.fncSplit(Descricao, '|', 4)
FROM
    dbo.Teste_Group_Concat

Resultado:
SQL Server - Split function CLR TSQL performance

Como vocês podem observar, a tabela de testes possui 81.753 linhas, onde cada linha possui 4 palavras separadas pelo caracter “|”, e apliquei a função 4 vezes, para recuperar cada uma dessas palavras, de cada linha.

O resultado apresentou um argumento bem convincente para utilizar a função CLR: Enquanto a função T-SQL demorou 213,2 segundos para realizar esse processamento, a função CLR (que faz a mesma coisa) precisou de apenas 3,6 segundos. Uma diferença de performance de quase 60 vezes a mais, se utilizando o CLR.
Incrível!

sql server split explode divide string strings table valued function

sql server split explode divide string strings table valued function

Simples assim!
Até o próximo post.

Tags:

You may also like...

5 Responses

  1. Nego disse:
    27 de fevereiro de 2018 às 16:45

    Muito boa a função… Parabéns !

    Responder
  2. Osmar Fernandes disse:
    24 de junho de 2016 às 15:32

    Criei a função mas só esta trazendo NULL como resultado.

    Poderia me ajudar?

    Responder
  3. Odilon AM disse:
    21 de setembro de 2015 às 14:46

    Me quebrou um galho imenso, Valeu!

    Responder
  4. israelsep disse:
    13 de fevereiro de 2015 às 16:46

    Muito útil, obrigado!

    Responder

Deixe uma resposta