Hey guys,
Good afternoon!

In this post I will demonstrate a really cool table-valued function that allows you to break a string into a table of substrings using a delimiter in SQL Server. What does this mean? Well, this makes it look like you have a string and the function will transform this string into a table using a separator. In this table, each record will be a part of the string at index i+1 and you can easily work with these substrings.

Interested in learning more about splits?

Examples of using the function

SQL Server - fncSplitTexto Como quebrar um string em uma tabela de substrings utilizando um delimitador
SQL Server - fncSplitTexto How to break a string into a table of substrings using a delimiter

How to implement this in your SQL Server database

This is the coolest part: Get to work!
View source code

CREATE FUNCTION [dbo].[fncSplitTexto] (
    @Ds_Texto VARCHAR(MAX),
    @Ds_Delimitador VARCHAR(100)
)
RETURNS @Tabela_Palavras TABLE
(
    Id INT,
    Palavra VARCHAR(MAX)
)
AS
BEGIN

    DECLARE
        @Ds_String VARCHAR(MAX),
        @Ds_Palavra VARCHAR(MAX) = '',
        @Qt_Palavras INT = 1


    IF (LEN(@Ds_Texto) > 0)
       SET @Ds_Texto = @Ds_Texto + @Ds_Delimitador   

    
    WHILE (LEN(@Ds_Texto) > 0)
    BEGIN  
    
        
        SET @Ds_String = LTRIM(SUBSTRING(@Ds_Texto, 1, CHARINDEX(@Ds_Delimitador, @Ds_Texto) - 1))  


        IF (@Ds_Palavra = ' ')
            SET @Ds_Palavra = '' 
        
        
        IF ((@Qt_Palavras = 1 AND LEN(@Ds_Palavra) > 0) OR @Qt_Palavras > 1)
        BEGIN
            
            INSERT INTO @Tabela_Palavras ( Id, Palavra )
            VALUES ( @Qt_Palavras, @Ds_Palavra ) 
                
            SET @Qt_Palavras = @Qt_Palavras + 1
            
        END
        
        
        SET @Ds_Palavra = @Ds_String
        SET @Ds_Texto = SUBSTRING(@Ds_Texto, CHARINDEX(@Ds_Delimitador, @Ds_Texto) + 1, LEN(@Ds_Texto))
          
    END  


    -- Insere o resto do texto
    INSERT INTO @Tabela_Palavras ( Id, Palavra )
    VALUES ( @Qt_Palavras, @Ds_Palavra ) 


    RETURN


END

Creating the function with the CLR

Another alternative to using this resource is through SQLCLR, a resource that allows you to create .NET code within the database and generally achieves much better performance than Transact-SQL. If you don't know CLR, find out more by accessing the post Introduction to SQL CLR (Common Language Runtime) in SQL Server.

View C# (CLR) function source code
using System.Collections;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{

    private class SplitTabela
    {

        public readonly SqlInt32 Id;
        public SqlString Ds_Palavra;

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

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

        var splitTabelaCollection = new ArrayList();

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

        var arrPalavras = Ds_Texto.Trim().Split(Ds_Separador.ToCharArray());

        for (var index = 0; index < arrPalavras.Length; index++)
        {

            var palavra = arrPalavras[index];

            splitTabelaCollection.Add(new SplitTabela(
                (index + 1),
                palavra
            ));

        }

        return splitTabelaCollection;

    }

    protected static void FillSplitTabela(object objSplitTabela, out SqlInt32 id, out SqlString dsPalavra)
    {

        var splitTabela = (SplitTabela) objSplitTabela;

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

    }

};

Simple and practical, right?
Hugs!

sql server tsql query table function split explodes text string substring function break word using delimiter delimiter

sql server tsql query table function split explodes text string substring function break word using delimiter delimiter