Split function by comma in SQL Server 2008

问题:

I know that this question has been asked many times but could not find what I needed.

I have this column "Order" which contains data in the following format. 'xxx,yyy,zzzz'
Now when I do my select statement I need to populate 3 columns by splitting this one

E.G.

Select Name,
    Surname,
    FirstCommaColumn=xx.UpToFirstColumn
    SecondCommaColumn=xx.FromFirstCommaToLastComma,
    ThirdColumnFromSecondCommaOnwards=FromSecondCommaToEnd
from myTable
--thought of doing something like
CROSS APPLY (SELECT TOP 1 * FROM dbo.SplitFunctionIDontHave(order,',')) AS xx

There are some rows which have no commas so I must return blank. I don't mind if I do in a function or within the query itself just not sure how to do this.

How can I do this using SQL Server 2008? This select is part of a view if makes a difference

回答1:

I've change the function name so it won't overlapped in what the Split() function really does.

Here is the code:

CREATE FUNCTION dbo.GetColumnValue(
@String varchar(8000),
@Delimiter char(1),
@Column int = 1
)
returns varchar(8000)
as     
begin

declare @idx int     
declare @slice varchar(8000)     

select @idx = 1     
    if len(@String)<1 or @String is null  return null

declare @ColCnt int
    set @ColCnt = 1

while (@idx != 0)
begin     
    set @idx = charindex(@Delimiter,@String)     
    if @idx!=0 begin
        if (@ColCnt = @Column) return left(@String,@idx - 1)        

        set @ColCnt = @ColCnt + 1

    end

    set @String = right(@String,len(@String) - @idx)     
    if len(@String) = 0 break
end 
return @String  
end

And here is the usage:

select dbo.GetColumnValue('Col1,Field2,VAlue3', ',', 3)

回答2:

Declare @str as Varchar(100) = '10|20|30|40|500|55'
Declare @delimiter As Varchar(1)='|'
Declare @Temp as Table ( item varchar(100))
Declare @i as int=0
Declare @j as int=0
Set @j = (Len(@str) - len(REPLACE(@str,@delimiter,'')))
While @i  < = @j
Begin
  if @i  < @j
  Begin
      Insert into @Temp 
      Values(SUBSTRING(@str,1,Charindex(@delimiter,@str,1)-1))
      set @str = right(@str,(len(@str)- Charindex(@Delominator,@str,1)))
  End
  Else
  Begin
     Insert into @Temp Values(@str)
  End

 Set @i = @i + 1
End

Select * from @Temp 

回答3:

There is no Split() function in SQL Server, but you can create user defined function.

see this answer How to split a comma-separated value to columns

回答4:

create the below function and use as below

CREATE FUNCTION [dbo].[Split]    
 (    
  @List nvarchar(2000),    
  @SplitOn nvarchar(5)    
 )      
 RETURNS @RtnValue table     
 (    

  Id int identity(1,1),    
  Value nvarchar(100)    
 )     
 AS      
 BEGIN     
  While (Charindex(@SplitOn,@List)>0)    
  Begin    

   Insert Into @RtnValue (value)    
   Select     
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))    

   Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))    
  End    

  Insert Into @RtnValue (Value)    
  Select Value = ltrim(rtrim(@List))    

  Return    
 END  

SELECT TOP 1 * FROM dbo.Split(order,',')

回答5:

it seems like a good case to play with parsename.

Edited to use @Order as example:

DECLARE @Order VARCHAR(MAX) = 'xxx,yyy,zzzz'
SELECT FirstCommaColumn=PARSENAME(REPLACE(@Order,',','.'),3),
       SecondCommaColumn=PARSENAME(REPLACE(@Order,',','.'),2),
       ThirdColumnFromSecondCommaOnwards=PARSENAME(REPLACE(@Order,',','.'),1)

回答6:

CREATE FUNCTION [dbo].[splitStr] ( 

@str NVARCHAR(MAX), 

    @delimiter CHAR(1) 
) 

RETURNS @output TABLE(

    RowID   smallint IDENTITY(1,1), 
    t1 NVARCHAR(MAX) ,
    t2 NVARCHAR(MAX) ,
    t3 NVARCHAR(MAX) ,
    t4 NVARCHAR(MAX) ,
    t5 NVARCHAR(MAX) ,
    t6 NVARCHAR(MAX) ,
    t7 NVARCHAR(MAX) ,
    t8 NVARCHAR(MAX) ,
    t9 NVARCHAR(MAX) ,
    t10 NVARCHAR(MAX)   
) 

begin

    declare @st int, @en int, @xx int
    declare @cntr int

    set @cntr = 0
    set @st = 1
    select @en = CHARINDEX(@delimiter, @str, @st)

    if @en = 0 
        set @en = LEN(@str)

    while @en <= LEN(@str) and @cntr < 11 begin
        set @cntr = @cntr + 1
        set @xx = @en - @st

        if @cntr = 1
            insert into @output(t1) values(SUBSTRING(@str, @st, @xx))
        if @cntr = 2 
            update @output set t2 = SUBSTRING(@str, @st, @xx) 
        if @cntr = 3 
            update @output set t3 = SUBSTRING(@str, @st, @xx) 
        if @cntr = 4 
            update @output set t4 = SUBSTRING(@str, @st, @xx) 
        if @cntr = 5 
            update @output set t5 = SUBSTRING(@str, @st, @xx) 
        if @cntr = 6 
            update @output set t6 = SUBSTRING(@str, @st, @xx) 
        if @cntr = 7 
            update @output set t7 = SUBSTRING(@str, @st, @xx) 
        if @cntr = 8 
            update @output set t8 = SUBSTRING(@str, @st, @xx) 
        if @cntr = 9 
            update @output set t9 = SUBSTRING(@str, @st, @xx) 
        if @cntr = 10 
            update @output set t10 = SUBSTRING(@str, @st, @xx)

        set @st = @en + 1

        if @st > len(@str)
            begin
                set @en = @en + 100
            end
        else
            begin       
                select @en = CHARINDEX(@delimiter,@str, @st)
                if @en = 0 
                    begin
                        set @en = LEN(@str)
                        set @xx = @en - @st
                    end
            end

    end 

    return  

end

/*

This will allow you to split up to 10 fields out by delimiter. You can add more to the list if your needs exceed 10.

usage

select * from TableName a
cross apply splitStr(a.FiledName, ',')

*/