Use [TMS]
GO
/****** object:userdefinedfunction [dbo]. [Fn_strtotable_double] Script DATE:2017/4/26 9:07:38 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--AUTHOR:HXQ
--Create date:2016 August 9 18:04:11
--Description: Table-valued Function-splits an incoming string into a temporary table with a delimiter of 2, for example: SELECT * from dbo.fn_strtotable_double (' 111|222,333|444,555|666 ', ', ', ' | ')
-- =============================================
ALTER FUNCTION [dbo]. [Fn_strtotable_double]
(
@String NVARCHAR (max),--the string to convert.
@Separator1 VARCHAR (10) = ', ',--delimiter.
@Separator2 varchar (10) = ' | ' --A second delimiter
)
RETURNS @TableDouble TABLE ([ID] NVARCHAR (+), [Text] NVARCHAR (+), Sort int)
As
BEGIN
SET @String = @String [email protected]
DECLARE @int int=1
While CHARINDEX (@Separator1, @String) > 0
BEGIN
declare @str nvarchar (+) = ", @str1 nvarchar (500) ="
Set @str =substring (@String, 1, CHARINDEX (@Separator1, @String)-1)
SET @str1 =substring (@str, 1, CHARINDEX (@Separator2, @str)-1)
Set @str =substring (@str, CHARINDEX (@Separator2, @str) +1, CHARINDEX (@Separator2, @str)-1)
INSERT into @TableDouble ([Id],[text],sort) VALUES (@str1, @str, @int)
Set @[email protected]+1
SELECT @String = SUBSTRING (@String, CHARINDEX (@Separator1, @String) + 1, LEN (@String)-CHARINDEX (@Separator1, @String))
END
Return
END
SQL table-valued function-splits an incoming string into a temporary table with a delimiter of 2