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, Dec 11 '13 at 07:23
It would be much better to not be attempting to store *multiple* data items in a single column. — Damien_The_Unbeliever, Dec 11 '13 at 07:23
@Damien_The_Unbeliever,I have not got a choice I am importing this database into our system. — user9969, Dec 11 '13 at 07:28

score 13 · Accepted Answer · edited Dec 12 '15 at 20:44

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 '15 at 20:44

Sam

7,252
16
46
65

answered Dec 11 '13 at 08:02

Jade

2,972
1
11
9

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 . – user9969 Dec 11 '13 at 08:19
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 – Jade Dec 11 '13 at 08:22
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. – jediCouncilor Jun 27 '17 at 19:30

score 2 · Answer 2 · answered Apr 11 '17 at 10:18

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

score 1 · Answer 3 · edited May 23 '17 at 11:46

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 '17 at 11:46

Community

1
1

answered Dec 11 '13 at 07:25

Jade

2,972
1
11
9

,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? – user9969 Dec 11 '13 at 07:34
Please see my new posted answer. HOPE you like it. – Jade Dec 11 '13 at 08:02

score 0 · Answer 4 · answered Dec 11 '13 at 07:27

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

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 — user9969, Dec 11 '13 at 08:23

Jayvee · Answer 5 · 2013-12-11T09:07:58.970

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 '13 at 09:07

answered Dec 11 '13 at 08:44

Jayvee

10,670
3
29
40

The thread explicitly names SQL Server 2008, which doesn't have that function. – RBerman Apr 30 '19 at 20:06

score 0 · Answer 6 · edited Jun 16 '15 at 03:27

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

*/

score 0 · Answer 7 · answered May 17 '23 at 10:41

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

Split function by comma in SQL Server 2008

7 Answers7

Linked

Related