分隔符分解字符串

declare @str nvarchar(50);
set @str='462,464,2';
select @str as '字符串'
select len(@str) as '字符长度'
select charindex(',',@str,1) as '第一个逗号的索引值'
select LEFT(@str,charindex(',',@str,1)-1) as '第一个值'
select SUBSTRING(@str,charindex(',',@str,1)+1,len(@str)) as '从第一逗号开始截取出后面的字符串'
select LEFT(SUBSTRING(@str,charindex(',',@str,1)+1,len(@str)),CHARINDEX(',',SUBSTRING(@str,charindex(',',@str,1)+1,len(@str)),1)-1) as '中间的值'
select SUBSTRING(SUBSTRING(@str,charindex(',',@str,1)+1,len(@str)),charindex(',',SUBSTRING(@str,charindex(',',@str,1)+1,len(@str)),1)+1,len(@str)) as '最后面的值' --从第二个逗号开始截取出其后的字符串

 

---

如果有一个字符串 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

 

--獲取被分隔符分割開來的字符串中參數的個數
CREATE function GetStrArrayLength

(

 @str varchar(1024),  --要分割的字符串
 @split varchar(10)  --分隔符号
)
returns int
as
begin

 declare @location int

 declare @start int

 declare @length int


 set @str=ltrim(rtrim(@str))--剔除字符串的首尾空格

 set @location=charindex(@split,@str)--第一個分隔符在字符串中的索引數字  例如'a1,b2,c3,d4'中第一個','的位置索引

 set @length=1--字符串被分隔符分割開來的參數個數，這裡初始化為一個


 while @location<>0 --如果字符串中還存在分隔符那麼該分隔符在字符串中的索引就不會為0，也就是不等於0
 begin

   set @start=@location+1 --從第一個分隔符索引位置後的字符串中開始檢索，檢索的起始是第一個分隔符後的一位開始
   set @location=charindex(@split,@str,@start)

   set @length=@length+1

 end

 return @length
end
---------------------------------------
--获得带分隔符的字符串中指定索引对应的间隔项
creat FUNCTION dbo.f_GetStr(

    @s varchar(8000),      --包含多个数据项的字符串
    @pos int,             --要获取的数据项的位置
    @split varchar(10)     --数据分隔符
)RETURNS varchar(1000)
AS
BEGIN

    IF @s IS NULL RETURN(NULL)

    DECLARE @splitlen int

    SELECT @splitlen=LEN(@split+'a')-2

    WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0

        SELECT @pos=@pos-1,

            @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')

    RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
--------------------------------------------------------------

获得指定索引对应的间隔开来的单项字符数据
create function GetStr

(

 @str varchar(1024),  --要分割的字符串
 @split varchar(10),  --分隔符号
 @index int --取第几个元素
)
returns varchar(1024)
as
begin

 declare @location int

 declare @start int

 declare @next int

 declare @seed int


 set @str=ltrim(rtrim(@str))

 set @start=1

 set @next=1

 set @seed=len(@split)

 

 set @location=charindex(@split,@str)

 while @location<>0 and @index>@next

 begin

   set @start=@location+@seed

   set @location=charindex(@split,@str,@start)

   set @next=@next+1

 end

 if @location =0 select @location =len(@str)+1  

 return substring(@str,@start,@location-@start)
end

 

---

 --生成测试数据   
 Create table Tab([Col1] int,[COl2] nvarchar(5)) 
 Insert Tab 
 select 1,N'a,b,c' union all 
 select 2,N'd,e' union all 
 select 3,N'f' 
 Go 
  
--1. SQL2000用辅助表: 
 if object_id('Tempdb..#Num') is not null 
     drop table #Num 
 go 
 select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b 
 Select  
     a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)  
 from  
     Tab a,#Num b 
 where 
     charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=',' 
  
  
--2. SQL2005用Xml:   
 select  
     a.COl1,b.Col2 
 from  
     (select Col1,COl2=convert(xml,' <root> <v>'+replace(COl2,',',' </v> <v>')+' </v> </root>') from Tab)a 
 outer apply 
     (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b 
  
  
--3. SQL2005用CTE:   
 ;with roy as  
 (select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab 
 union all 
 select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>'' 
 ) 
 select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0) 
  
--生成结果: 
 /* 
 Col1        COl2 
 ----------- ----- 
          a 
          b 
          c 
          d 
          e 
          f 
 */