SQL字符串Split
代码:
GO
/****** Object: UserDefinedFunction [dbo].[StringSplit] Script Date: 2018/5/25 9:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[StringSplit]
(
@Text NVARCHAR(MAX),
@Sign NVARCHAR(MAX)
)
RETURNS
@ResultTable TABLE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
TextValue NVARCHAR(1024)
)
AS
BEGIN
DECLARE @StartIndex INT
DECLARE @FindIndex INT
DECLARE @Content VARCHAR(4000)
-- 和函数CHARINDEX有关CHARINDEX
SET @StartIndex = 1
SET @FindIndex=0
WHILE(@StartIndex<=LEN(@Text))
BEGIN
SET @FindIndex=CHARINDEX(@Sign, @Text, @StartIndex)
IF(@FindIndex=0 OR @FindIndex IS NULL)
BEGIN
-- 查找完毕
SET @FindIndex=LEN(@Text)+1
END
SET @Content=LTRIM(RTRIM(SUBSTRING(@Text, @StartIndex, @FindIndex-@StartIndex)))
-- 下次查找的位置
SET @StartIndex=@FindIndex+1
-- 插入结果
IF(LEN(@Content)>0)
BEGIN
INSERT INTO @ResultTable(TextValue) VALUES (@Content)
END
END
RETURN
END
GO
调用:
SELECT * FROM dbo.StringSplit('a,b,c,d,e,f,g', ',')
结果: