SQL根据指定分隔符分解字符串实现步骤

 代码如下: 
CREATE FUNCTION [dbo].[Split_StrByDelimiter](@String VARCHAR(8000), @Delimiter CHAR(1)) 
RETURNS @temptable TABLE (items VARCHAR(8000)) 
AS 
BEGIN 
DECLARE @idx INT 
DECLARE @slice VARCHAR(8000) 
SELECT @idx = 1 
IF len(@String)<1 OR @String IS NULL RETURN 
while @idx!= 0 
BEGIN 
SET @idx = charindex(@Delimiter,@String) 
IF @idx!=0 
SET @slice = LEFT(@String,@idx - 1) 
ELSE 
SET @slice = @String 
IF(len(@slice)>0) 
INSERT INTO @temptable(Items) VALUES(@slice) 
SET @String = RIGHT(@String,len(@String) - @idx) 
IF len(@String) = 0 break 
END 
RETURN 
END 
 
示例：如果输入 
SELECT * FROM dbo.Split_StrByDelimiter('sun,star,moon,clouds',',') 
结果返回 
sun 
star 
moon 
clouds 
在上面的代码做变形，返回有多少个元素 
 代码如下: 
CREATE FUNCTION [dbo].[GetCount_Split_StrByDelimiter](@String VARCHAR(8000), @Delimiter CHAR(1)) 
RETURNS INT 
AS 
BEGIN 
DECLARE @temptable TABLE (items VARCHAR(8000)) 
DECLARE @SplitCount INT 
DECLARE @idx INT 
DECLARE @slice VARCHAR(8000) 
SELECT @idx = 1 
IF len(@String)<1 OR @String IS NULL RETURN 0 
while @idx!= 0 
BEGIN 
SET @idx = charindex(@Delimiter,@String) 
IF @idx!=0 
SET @slice = LEFT(@String,@idx - 1) 
ELSE 
SET @slice = @String 
IF(len(@slice)>0) 
INSERT INTO @temptable(Items) VALUES(@slice) 
SET @String = RIGHT(@String,len(@String) - @idx) 
IF len(@String) = 0 break 
END 
SET @SplitCount=(SELECT COUNT(*) FROM @temptable) 
RETURN @SplitCount 
END 
 
示例 
SELECT dbo.GetCount_Split_StrByDelimiter('sun,star,moon,clouds',',') 
结果返回 
4
 
 
您可能感兴趣的文章:
sqlserver中去除字符串中连续的分隔符的sql语句
sql带分隔符的截取字符串示例