Thiago Zavaschi R2 www.zavaschi.com

1Jun/094

Repostagem – Função Split no SQL Server

 

Então pessoal, atenção que é uma repostagem! O post sobre Common Table Expressions (CTE) e recursividade já está postado! O de CLR ainda não hehe, mas um dia cumpro todas as promessas!

Função Split no SQL Server

Uma função que sempre senti falta no SQL Server é uma função para split, aonde passaríamos dois parâmetros:

    • Frase a ser “splitada”.
    • Delimitador.

E teríamos uma lista de palavras originadas da frase passada, separada em cada delimitador.

Já desenvolvi várias versões para fazer split, mas a que considero a melhor (e também bastante popular) é a seguinte:

CREATE FUNCTION dbo.fnSplit(
    @frase VARCHAR(max)
  , @delimitador VARCHAR(max) = ','
) RETURNS @result TABLE (item VARCHAR(8000)) 

BEGIN
DECLARE @parte VARCHAR(8000)
WHILE CHARINDEX(@delimitador,@frase,0) <> 0
BEGIN
SELECT
  @parte=RTRIM(LTRIM(
          SUBSTRING(@frase,1,
        CHARINDEX(@delimitador,@frase,0)-1))),
  @frase=RTRIM(LTRIM(SUBSTRING(@frase,
          CHARINDEX(@delimitador,@frase,0)
        + LEN(@delimitador), LEN(@frase))))
IF LEN(@parte) > 0
  INSERT INTO @result SELECT @parte
END 

IF LEN(@frase) > 0
INSERT INTO @result SELECT @frase
RETURN
END
GO

Para testar é simples:

SELECT * FROM dbo.fnSplit('separar por espaço em branco', ' ')

----

Se desejar, há outra forma, utilizando tabela temporária, sem função:

SET NOCOUNT ON  
DECLARE @ARRAY VARCHAR(8000), @DELIMITADOR VARCHAR(100), @S VARCHAR(8000)  

SELECT @ARRAY = 'separar por espaço em branco'
SELECT @DELIMITADOR = ' '  

IF LEN(@ARRAY) > 0 SET @ARRAY = @ARRAY + @DELIMITADOR   
CREATE TABLE #ARRAY(ITEM_ARRAY VARCHAR(8000))  

WHILE LEN(@ARRAY) > 0  
BEGIN  
    SELECT @S = LTRIM(SUBSTRING(@ARRAY, 1, 
    CHARINDEX(@DELIMITADOR, @ARRAY) - 1))  
    INSERT INTO #ARRAY (ITEM_ARRAY) VALUES (@S)  
    SELECT @ARRAY = SUBSTRING(@ARRAY, 
    CHARINDEX(@DELIMITADOR, @ARRAY) + 1, LEN(@ARRAY))  
END  

-- MOSTRANDO O RESULTADO JÁ POPULADO NA TABELA TEMPORÁRIA  
SELECT * FROM #ARRAY  
DROP TABLE #ARRAY  
SET NOCOUNT OFF

 

Simples não?

Estas maneiras mostradas resolvem a necessidade da falta do split? Sim!

Porém, desde o SQL Server 2005 temos o recurso das CTEs (Common Table Expression), que também podem ser utilizadas para split. A seguir mostro o exemplo que considero mais elegante para tal tarefa, utiliza recursividade ao invés do loop:

DECLARE @s VARCHAR(8000), @d VARCHAR(10)
SET @s = 'separar por espaço em branco'
SET @d = ' ' 

;WITH split(i,j) AS
(
SELECT i = 1, j = CHARINDEX(@d, @s + @d)
UNION ALL
SELECT i = j + 1, j = CHARINDEX(@d, @s + @d, j + 1) FROM split
   WHERE CHARINDEX(@d, @s + @d, j + 1) <> 0
)
SELECT SUBSTRING(@s,i,j-i)
FROM split

Observação importante sobre CTEs:

Ao utilizar CTEs recursivas devemos tomar cuidado com o número máximo de recursões permitidas.

Por padrão esse número é igual a 100. Isso na prática significa que eu só poderia ter 100 delimitadores dentro da minha variável @s.

Caso a recursão máxima seja alcançada, termos uma mensagem igual a essa:

“The statement terminated. The maximum recursion 100 has been exhausted before statement completion.”

Para aumentar este valor, podemos acionar um parâmetro extra para a CTE: maxrecursion.

Na prática, para trocar a recursão máxima para 1000, devemos fazer:

DECLARE @s VARCHAR(8000), @d VARCHAR(10)
SET @s = 'separar por espaço em branco'
SET @d = ' ' 

;WITH split(i,j) AS
(
SELECT i = 1, j = CHARINDEX(@d, @s + @d)
UNION ALL
SELECT i = j + 1, j = CHARINDEX(@d, @s + @d, j + 1) FROM split
   WHERE CHARINDEX(@d, @s + @d, j + 1) <> 0
)
SELECT SUBSTRING(@s,i,j-i)
FROM split
OPTION (maxrecursion 1000)

 

O valor máximo para o parâmetro MAXRECURSION é de 32767, então fiquem atentos. Caso seja necessário uma CTE recursiva com mais de 32767 iterações, então devemos pensar numa estratégia para executar mais de um split (ou mais de uma vez a operação desejada) por iteração. Em casos muito específicos, podemos deixar o máximo de recursão infinito (MAXRECURSION = 0).

Não entendeu muito bem o que é uma CTE? Pode recorrer ao BOL, ou esperar o meu próximo post (há este e o de cursores para postar) que abordarei BEM detalhadamente o que é uma CTE, sintaxe, como funciona, seus usos comuns, e a questão de como funciona a recursividade. Então fiquem atentos! 

---

Depois destas três maneiras mostradas, ainda há uma quarta possibilidade (a partir do SQL Server 2005 também) que é utilizar as chamadas Functions CLR (programadas em C# por exemplo). O por quê? Porque operações em strings são custosas para o SQL Server, então uma função CLR poderia se comportar de maneira muito interessante (performática) neste caso.

Também não sabe o que é CLR ou como funciona? Então aguarde os próximos posts também!

Mas eu ainda continuo na esperança de algum dia termos isso diretamente no SQL Server, de uma maneira mais otimizada. :)

Abraços,

Thiago Zavaschi

Comments (4) Trackbacks (0)
  1. Agradeço o post, foi de muita utilidade pra mim.

    Abraço!

  2. Muito bom.

  3. Muito bom seu post amigo, porém há um problema na forma recursiva!

    Se nós colocarmos dois argumentos de pesquisa “braco”, por exemplo, ele mela o resultado: “123345″ por exemplo na forma recursiva ele traz 12 e 45 quando deveria trazer 12,,45.

    Grande abraço!

  4. Desculpe-me,
    no comentário acima utilizei o “3″ como argumento de pesquisa.


Leave a comment


No trackbacks yet.