How to wrap a string in a substring table using a delimiter in SQL Server

Views: 6.528 views

In this article

This post is the 3 part of 5 in the series. string Split

SQL Server - Breaking strings into sub-strings using a separator (Split string)
SQL Server - Table-valued function to break a string into lines up to N characters long
How to wrap a string in a substring table using a delimiter in SQL Server
SQL Server - Charindex: A Different Function to Break Split Strings
SQL Server - Using STRING_SPLIT to transform strings from a row into columns

Reading Time: 2 minutes

Hello people,
Good afternoon!

In this post I will demonstrate a very nice table-valued function that lets you break a string in a substring table using a delimiter in SQL Server. What does that mean? Well, this makes you have a string and the function will turn that string into a table using a separator. In this table, each record will be a part of the string in the i + 1 index and you can easily work with these substrings.

Interested in learning more about split?

Function Usage Examples

How to implement this in your SQL Server database

This is the cool part: Get to work!
View source

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

CREATE FUNCTION [dbo].[fncSplitTexto] (

@Ds_Texto VARCHAR(MAX),

@Ds_Delimitador VARCHAR(100)

)

RETURNS @Tabela_Palavras TABLE

(

Id INT,

Palavra VARCHAR(MAX)

)

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 feature is through SQLCLR, a feature that allows you to create .NET code within the database and generally performs much better than Transact-SQL. If you do not know the CLR, learn more by accessing the post. Introduction to SQL Common Language Runtime (CLR) in SQL Server.

View C # Function (CLR) 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;

    }

};

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 explode text string substring break word function using delimiter delimiter

Virgil said:

1 April 2021 20 gies: 33

Error executing the penultimate script:

Msg 7738, Level 16, State 2, Line 1
Cannot enable compression for object 'xyz'. Only SQL Server Enterprise Edition supports compression.

Under development

November 18th, 2021

[…] return. With that, I ended up using other alternatives myself, as I demonstrated in the article How to break a string in a table of substrings using a delimiter in SQL Server and that works in any version of SQL […]

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

How to wrap a string in a substring table using a delimiter in SQL Server

Function Usage Examples

How to implement this in your SQL Server database

Creating the function with the CLR

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

You may also like ...

Comments

Leave a Comment Cancel reply

Function Usage Examples

How to implement this in your SQL Server database

Creating the function with the CLR

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

You may also like ...

SQL Server - How to query the national, state, municipal, and optional holidays of an API using OLE Automation and CLR (C #)

How was the 7th PASS Local Group SQL Server ES meeting?

SQL Server - How to encrypt and decrypt passwords (with Salt) using CLR (C #)

Comments

Leave a Comment Cancel reply