Thiago Zavaschi R2 www.zavaschi.com

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