SQL Server Split function supports delimiter multibyte

SQL Server Split function

--Description:

--Support for multi-byte delimiters

--How to use

--select * from DBO. F_sqlserver_split (' 1203401230105045 ', ' 0 ')

--select * from DBO. F_sqlserver_split (' abc1234a12348991234 ', ' 1234 ')

--select * from DBO. F_sqlserver_split (' ABC ', ', ')

ALTER FUNCTION [dbo]. [F_sqlserver_split] (@Long_str varchar (8000), @split_str varchar (100))

RETURNS @tmp TABLE (

ID InT IDENTITY PRIMARY KEY,

SHORT_STR varchar (8000)

)

As

BEGIN

DECLARE @long_str_Tmp varchar (8000), @short_str varchar (8000), @split_str_length int

SET @split_str_length = LEN (@split_str)

IF CHARINDEX (@split_str, @Long_str) =1

SET @long_str_Tmp =substring (@Long_str, @split_str_length +1,len (@Long_str) [email protected]_str_length)

ELSE

SET @[email Protected]_str

IF CHARINDEX (REVERSE (@split_str), REVERSE (@long_str_Tmp)) >1

SET @[email Protected][email Protected]_str

ELSE

SET @[email protected]_str_tmp

IF CHARINDEX (@split_str, @long_str_Tmp) =0

Insert into @tmp Select @long_str_Tmp

ELSE

BEGIN

While CHARINDEX (@split_str, @long_str_Tmp) >0

BEGIN

SET @short_str =substring (@long_str_Tmp, 1,charindex (@split_str, @long_str_Tmp)-1)

DECLARE @long_str_Tmp_LEN int, @split_str_Position_END int

SET @long_str_Tmp_LEN = LEN (@long_str_Tmp)

SET @split_str_Position_END = LEN (@short_str) [Email protected]_str_length

SET @long_str_Tmp =reverse (SUBSTRING (REVERSE (@long_str_Tmp), 1,@[email protected]_str_position_end))

IF @short_str <> ' Insert into @tmp select @short_str

END

END

RETURN

END

Invocation mode: Select Short_str from F_sqlserver_split (@StoreId, ',')

SQL Server Split function supports delimiter multibyte