代码
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
BEGIN
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
END
DECLARE @s NVARCHAR ( 1000 )
IF @p = 0 AND @i = @n
BEGIN
SET @s = @Expression
END
ELSE
IF @i = @n
BEGIN
SET @s = SubString ( @Expression , 1 , @p - Len ( @Delimiter ))
END
RETURN @s
END
(
@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
BEGIN
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
END
DECLARE @s NVARCHAR ( 1000 )
IF @p = 0 AND @i = @n
BEGIN
SET @s = @Expression
END
ELSE
IF @i = @n
BEGIN
SET @s = SubString ( @Expression , 1 , @p - Len ( @Delimiter ))
END
RETURN @s
END
使用方法:
代码
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
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