set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[str2tempTable] (@p_string VARCHAR (3000),
@p_sep VARCHAR (20) = ',')
RETURNS @tmp TABLE (tempstr VARCHAR(50))
AS
BEGIN
DECLARE @beginSearchPos INT
DECLARE @nextSearchPos INT
DECLARE @length INT
DECLARE @tempSqlStr VARCHAR(3000)
SET @beginSearchPos = 1
SET @nextSearchpos = 0
SET @length = len (@p_string)
WHILE @beginSearchPos <> 0 AND @nextSearchPos < @length
BEGIN
SET @nextSearchPos =
charindex (@p_sep, @p_string, @beginSearchPos)
IF @nextSearchPos = 0
BEGIN
INSERT @tmp SELECT substring (@p_string,
@beginSearchPos,
@length - @beginSearchPos + 1)
SET @beginSearchPos = 0
END
ELSE
BEGIN
INSERT @tmp SELECT substring (@p_string,
@beginSearchPos,
@nextSearchPos - @beginSearchPos)
SET @beginSearchPos = @nextSearchPos + 1
END
END
RETURN
END
实例:
select * from dbo.str2tempTable('123', ',');
第一个参数为传进去的字符串,第二个参数为分隔符!