学步园

   如果有一个字符串 eg: "sun,star,moon,clouds",想要在MS SQL中根据给定的分隔符','把这个字符串分解成各个元素[sun] [star] [moon] [clouds]，如何实现呢？为此，创建一个Function,代码如下:

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