Using specific characters to split strings in SQL Server

Time:2020-10-23

In T-SQL, we often split strings in batch operation, butThere is no split function in SQL serverSo it’s up to you. Here, the string is split and output in the form of table
The grammar is as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    /*
    create by shuke.li 2020-9-15
    */
    create function [dbo].[SplitString]
    (
        @Input nvarchar(max), --input string to be separated
        @Separator nvarchar(max)=',', --a string that delimit the substrings in the input string
        @RemoveEmptyEntries bit=1 --the return value does not include array elements that contain an empty string
    )
    returns @TABLE table 
    (
        [Id] int identity(1,1),
        [Value] nvarchar(max)
    ) 
    as
    begin 
        declare @Index int, @Entry nvarchar(max)
        set @Index = charindex(@Separator,@Input)

    while (@Index>0)
    begin
        set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))
        
        if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
            begin
                insert into @TABLE([Value]) Values(@Entry)
            end

        set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))
        set @Index = charindex(@Separator, @Input)
    end
    
    set @Entry=ltrim(rtrim(@Input))
    if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
        begin
            insert into @TABLE([Value]) Values(@Entry)
        end
    return
end

As long as the above code is executed in the new query, the split function is established.
Let’s test the function of this function. The SQL script used in the test is as follows:

declare @str1 varchar(max)
set @str1 = 'CFER-3345-3323,CFER-0023-2299,CFER-0023-6677,CFER-0023-7678,CFER-4565-2299,CFER-0023-6678'

select * from [dbo].[SplitString](@str1, ',', 0)

The results are as follows:

 

The final use of the method and C ා split function is not very similar!

Recommended Today

Comparison and analysis of Py = > redis and python operation redis syntax

preface R: For redis cli P: Redis for Python get ready pip install redis pool = redis.ConnectionPool(host=’39.107.86.223′, port=6379, db=1) redis = redis.Redis(connection_pool=pool) Redis. All commands I have omitted all the following commands. If there are conflicts with Python built-in functions, I will add redis Global command Dbsize (number of returned keys) R: dbsize P: print(redis.dbsize()) […]