SQL中的split方法的使用

SQL中的split方法的使用

参数说明：

1、@String ：需要split的字符串

2、@Delimiter ：格式化时分隔符

3、@index ：返回split后数组的值

ALTER FUNCTION [dbo].[GetCount_Split_StrByDelimiter](@String VARCHAR(8000), @Delimiter CHAR(1),@index char(1))
RETURNS varchar(100)
AS
BEGIN
DECLARE @temptable TABLE (items VARCHAR(8000))
DECLARE @SplitCount varchar(100)
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
if @index='1'
SET @SplitCount=(SELECT top 1 items FROM @temptable)

if @index='2'
SET @SplitCount=(SELECT top 1 items FROM @temptable where items not in (SELECT top 1 items FROM @temptable))

if @index='3'
SET @SplitCount=(SELECT top 1 items FROM @temptable where items not in (SELECT top 2 items FROM @temptable))

if @index='4'
SET @SplitCount=(SELECT top 1 items FROM @temptable where items not in (SELECT top 3 items FROM @temptable))
RETURN @SplitCount
END