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 

 

转载于:https://www.cnblogs.com/sayshap/p/8184879.html