Split function by comma in SQL Server 2008

Question

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

Jade,I know there isnt I am looking for one that does what I need or some code to split the column. — user9969
– user9969, Commented Dec 11, 2013 at 7:23
It would be much better to not be attempting to store multiple data items in a single column. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Dec 11, 2013 at 7:23
@Damien_The_Unbeliever,I have not got a choice I am importing this database into our system. — user9969
– user9969, Commented Dec 11, 2013 at 7:28

Sam · Accepted Answer · 2015-12-12 20:44:47Z

13

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)

edited Dec 12, 2015 at 20:44

Sam

7,41616 gold badges48 silver badges68 bronze badges

answered Dec 11, 2013 at 8:02

Jade

2,9941 gold badge13 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user9969 Over a year ago

thanks for your time,if I understand it correctly to get the 3 columns split I have to call the function 3 times eg SELECT [dbo].[Split] ( 'xx, yy,zz',',',1) would give xx with 2 yy and with 3 zz .

Jade Over a year ago

Yes. And i think your using it in TSQL so this will be the best thing you can do. And i preferred to use GetColumnValue rather than Split

jediCouncilor Over a year ago

Nice work! I hadn't needed to do this in a while, so this was useful! I loaded the results into a temp table and incremented the column by 1 each time through the loop. At the end I used a not exists to find out when to break my loop.

Omkar Naik · Accepted Answer · 2017-04-11 10:18:22Z

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

answered Apr 11, 2017 at 10:18

Omkar Naik

511 bronze badge

Comments

Community · Accepted Answer · 2017-05-23 11:46:34Z

1

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

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Dec 11, 2013 at 7:25

Jade

2,9941 gold badge13 silver badges9 bronze badges

2 Comments

user9969 Over a year ago

,thanks for the split function but how do I use it my context,eg filling the 3 columns i talked in the question? Sorry if obvious but cannot see how you do it.That split will give me only up to the first comma,what about from firstcomma to second and the last?

Jade Over a year ago

Please see my new posted answer. HOPE you like it.

Indranil.Bharambe · Accepted Answer · 2013-12-11 07:27:32Z

0

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,',')

answered Dec 11, 2013 at 7:27

Indranil.Bharambe

1,4983 gold badges14 silver badges26 bronze badges

1 Comment

user9969 Over a year ago

that seems to return a table and what I want.If i pass SELECT * FROM [dbo].[Split] ('xx, yy,zz',',') i get a table with id value how do i use in a select statement to fill the columns eg Select Name, Surname, FirstCommaColumn=? SecondCommaColumn=? ThirdColumnFromSecondCommaOnwards=? from myTable CROSS APPLY (SELECT TOP 1 * FROM dbo.Split(orderColumn,',')) AS xx

Jayvee · Accepted Answer · 2013-12-11 09:07:58Z

0

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)

edited Dec 11, 2013 at 9:07

answered Dec 11, 2013 at 8:44

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

1 Comment

RBerman Over a year ago

The thread explicitly names SQL Server 2008, which doesn't have that function.

shA.t · Accepted Answer · 2015-06-16 03:27:54Z

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, ',')

*/

M.Vakili · Accepted Answer · 2023-05-17 10:41:29Z

Returning a table as the answer and internal string controller There is no particular difference with the answers of other friends. It was just code optimizationReturning a table as the answer and internal string controller There is no particular difference with the answers of other friends. It was just code optimization

CREATE FUNCTION dbo._SplitString
(
  @String        VARCHAR(Max)
 ,@Delimiter     CHAR(1)
   
)
RETURNS @Ans Table (
         Value NVarChar(20))
AS
BEGIN
    IF RIGHT(@String,1)<>@Delimiter
       SET @String=@String+@Delimiter
           
    WHILE (LEN(ISNULL(@String,''))>0)
    BEGIN
           IF LEFT(@String ,CHARINDEX(@Delimiter ,@String)- 1)<>''
            INSERT INTO @Ans VALUES (
                                      LEFT(@String ,CHARINDEX(@Delimiter ,@String)- 1) 
        )       
        SET @String = RIGHT(@String ,LEN(@String)- CHARINDEX(@Delimiter ,@String)) 

    END
    
    RETURN 
END

Collectives™ on Stack Overflow

Split function by comma in SQL Server 2008

7 Answers 7

3 Comments

Comments

2 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

Comments

2 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related