代碼
CREATE FUNCTION dbo.SplitString
(
@Expression NVARCHAR(4000), --要拆分的字元串
@Delimiter NVARCHAR(100), --拆分符号
@n INT --要得到已拆分的傳回字元串位置
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @p INT
SET @p = CharIndex(@Delimiter,@Expression)
IF @p > 0
BEGIN
SET @p = @p + Len(@Delimiter) - 1
END
DECLARE @i INT
SET @i = 1
WHILE @i < @n
SET @i = @i + 1
SET @Expression = SubString(@Expression, @p + 1,Len(@Expression) - @p )
SET @p = CharIndex(@Delimiter,@Expression)
IF @p > 0
BEGIN
SET @p = @p + Len(@Delimiter) - 1
END
ELSE
BEGIN
BREAK
END
DECLARE @s NVARCHAR(1000)
IF @p = 0 AND @i = @n
SET @s = @Expression
ELSE
IF @i = @n
SET @s = SubString(@Expression, 1,@p - Len(@Delimiter))
RETURN @s
使用方法：
DECLARE @string NVARCHAR(50)
SET @string = '在|SQL SERVER|中實作Split|功能的|函數|'
DECLARE @split NVARCHAR(4)
SET @split = '|'
SELECT dbo.SplitString (@string, @split, 1) --在
SELECT dbo.SplitString (@string, @split, 2) --SQL SERVER
SELECT dbo.SplitString (@string, @split, 3) --中實作Split
SELECT dbo.SplitString (@string, @split, 4) --功能的
SELECT dbo.SplitString (@string, @split, 5) --函數
SELECT dbo.SplitString (@string, @split, 6) --null