--SQL Server Split function --author:sq-- Description: Split field: Short_str--Support for multi- byte delimiter --
--select * from splits ('1203401230105045','0'
--select * from splits (' 1203401230105045 ', ' 123 '
-- Cross Apply Splits (T1.name, ', ') as T2
Cross apply is a function of more than 2005 versions
CREATE FUNCTION Splits (@Long_str NVARCHAR (MAX), @split_str NVARCHAR ( -) ) RETURNS @tmp TABLE (ID inT IDENTITY PRIMARY KEY, Short_str NVARCHAR (max)) as BEGIN DECLARE @short_str NVARCHAR (max ), @split_str_lengthint, @split_str_Position_BeginintSET @split_str_length=LEN (@split_str) SET @Long_str=replace (REPLACE (@Long_str, CHAR (Ten),"'), CHAR ( -),"') IF CHARINDEX (@split_str, @Long_str)=1SET @Long_str=stuff (@Long_str,1, @split_str_length,"') IF CHARINDEX (@split_str, @Long_str)=0INSERT into @tmp SELECT @Long_str elsebeginwhile1>0BEGIN SET @split_str_Position_Begin=CHARINDEX (@split_str, @Long_str) SET @short_str=left (@Long_str, @split_str_Position_Begin-1) IF @short_str<>"'INSERT into @tmp SELECT @short_str SET @Long_str=stuff (@Long_str,1, @[email protected]_str_length-1,"') SET @split_str_Position_Begin=CHARINDEX (@split_str, @Long_str) IF @split_str_Position_Begin=0beginif LTRIM (@Long_str)<>"'INSERT into @tmp SELECT @Long_str breakendend endreturn END
SQL Server custom string segmentation function.