Splitting a string into parts - not as rows. 1

[wvdba]

Hi,
i have a database table column whose content looks like 'abc-1-a-2-01'.
i know i can use split(column_name, '-'). this gives you 5 rows of data like
abc
1
a
2
01
however, i like to split this into 5 separate columns like
abc 1 a 2 01
any help would be greatly appreciated.
thanks.

 

[philhege]

Split()? What database and version are you using?

-----------
With business clients like mine, you'd be better off herding cats.

 

[wvdba]

sql server 2008 R2
i think i already mentioned in my post that I DID use split() which gives me five rows.
i want five columns.
thanks.

 

[gmmastros]

Create Function [dbo].[SplitPart](@CommaDelimitedFieldNames Varchar(Max), @CharToFind VarChar(10), @RowToReturn int) 
Returns varchar(8000)
AS
  Begin 
		
    declare @Tbl_FieldNames Table (Position Integer Identity(1,1), FieldName VarChar(8000)) 

    Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @CharToFind
    Declare @Pos1 Int
    Declare @pos2 Int

    Set @Pos1=1
    Set @Pos2=1
    While @Pos1<Len(@CommaDelimitedFieldNames)
      Begin
        Set @Pos1 = CharIndex(@CharToFind, @CommaDelimitedFieldNames,@Pos1)
        Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(8000))
        Set @Pos2=@Pos1+len(@CharToFind)
        Set @Pos1 = @Pos1+Len(@CharToFind)
      End 

    Return (Select FieldName From @Tbl_FieldNames Where Position = @RowToReturn)
  End

Use it like this:

Select dbo.SplitPart('abc-1-a-2-01', '-', 1),
       dbo.SplitPart('abc-1-a-2-01', '-', 2),
       dbo.SplitPart('abc-1-a-2-01', '-', 3),
       dbo.SplitPart('abc-1-a-2-01', '-', 4),
       dbo.SplitPart('abc-1-a-2-01', '-', 5),
       dbo.SplitPart('abc-1-a-2-01', '-', 6)

Fair warning... this code will NOT be fast.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[philhege]

Thanks for the reply, George.

wvdba, this is what I was getting at when I asked my question:

select dbo.split('what-what-what','-')
Msg 195, Level 15, State 10, Line 1
'split' is not a recognized built-in function name.

You can look into using PIVOT as a means of translating rows to columns. You can also read Erland Sommarskog's seminal articles on arrays and lists to give you some more ideas.

-----------
With business clients like mine, you'd be better off herding cats.

 

[RyanEK]

How about...

declare @temp table (id int, col varchar(50))
insert into @temp values (1, 'abc-1-a-2-01')
insert into @temp values (2, 'efg-2-b-3-02')

;with cte as 
( 
    select t2.id, t3.split, ROW_NUMBER() over (partition by t2.id order by t2.id) as num
    from (
	    select *,
        CAST('<i>'+replace(t.col,'-','</i><i>')+'</i>' as XML) as myfilter
        from @temp t
    ) t2
    cross apply
    (
        select coldata.D.value('.','varchar(50)') as split
        from t2.myfilter.nodes('i') as coldata(D)
	) t3
)
  
select id, [1], [2], [3], [4], [5]
from 
(
    select id, num, split
    from cte
) as sourcetable
pivot
(max(split) for num IN ([1], [2], [3], [4], [5])) as pivottable

Results:

id	1	2	3	4	5
1	abc	1	a	2	01
2	efg	2	b	3	02

Ryan