IF @idx!=0
IF len(@String)<1 OR @String IS NULL RETURN
DECLARE @slice VARCHAR(8000)
IF len(@String)<1 OR @String IS NULL RETURN 0
DECLARE @temptable TABLE (items VARCHAR(8000))
RETURN
IF(len(@slice)>0)
4 while @idx!= 0

AS
SELECT * FROM dbo.Split_StrByDelimiter('sun,star,moon,clouds',',')
ELSE
BEGIN
SET @String = RIGHT(@String,len(@String) - @idx)
SELECT @idx = 1
END
AS

复制代码 代码如下:

DECLARE @idx INT
CREATE FUNCTION [dbo].[GetCount_Split_StrByDelimiter](@String VARCHAR(8000), @Delimiter CHAR(1))
如果有一个字符串 eg: "sun,star,moon,clouds",想要在MS SQL中根据给定的分隔符','把这个字符串分解成各个元素[sun] [star] [moon] [clouds]，如何实现呢？为此，创建一个Function,代码如下:

在上面的代码做变形，返回有多少个元素
SET @slice = @String
结果返回
INSERT INTO @temptable(Items) VALUES(@slice)
BEGIN
star
while @idx!= 0
INSERT INTO @temptable(Items) VALUES(@slice)
DECLARE @SplitCount INT
SET @SplitCount=(SELECT COUNT(*) FROM @temptable)
DECLARE @slice VARCHAR(8000)
clouds
RETURNS INT
IF len(@String) = 0 break
moon
RETURNS @temptable TABLE (items VARCHAR(8000))

复制代码 代码如下:

示例：如果输入
SELECT @idx = 1

IF @idx!=0
SET @slice = @String
ELSE
SET @slice = LEFT(@String,@idx - 1)
RETURN @SplitCount
结果返回
SET @idx = charindex(@Delimiter,@String)
SET @idx = charindex(@Delimiter,@String)
sun
示例
SELECT dbo.GetCount_Split_StrByDelimiter('sun,star,moon,clouds',',')
END

CREATE FUNCTION [dbo].[Split_StrByDelimiter](@String VARCHAR(8000), @Delimiter CHAR(1))
IF(len(@slice)>0)

IF len(@String) = 0 break
DECLARE @idx INT
BEGIN
END
END
SET @String = RIGHT(@String,len(@String) - @idx)
BEGIN
SET @slice = LEFT(@String,@idx - 1)