Split into different columns SQL Server

Question

I've got data in a column of my table just like this:

cpc > cpc > organic
cpc > organic >cpc
mail > cpc > organic

How can I split that data to different columns using '>' as a delimiter?

http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — Paolo Costa, Apr 25 '15 at 19:00
So when I use that script in the link- how to make a split function from a certain column? it does not work when i try Select * from fn_CSVToTable (Select Column from Table) — Keithx, Apr 25 '15 at 20:43

score 2 · Accepted Answer · edited May 23 '17 at 11:59

2

This is famous problem. You can use Regex through CLR imported function, but here is very usefull script. Alter spliting you can use PIVOT/UNPIVOT is you need exactly data-by-columns.

edited May 23 '17 at 11:59

Community

1
1

answered Apr 25 '15 at 19:03

Vladimir Semashkin

1,270
1
10
21

Dudi Konfino · Answer 2 · 2015-04-25T20:20:37.710

0

SELECT 
    PARSENAME(REPLACE(String,'>','.'),1) AS 'col1' ,
    PARSENAME(REPLACE(String,'>','.'),2) AS 'col2' ,
    PARSENAME(REPLACE(String,'>','.'),3) AS 'col3'
FROM tbl

edited Apr 25 '15 at 20:20

answered Apr 25 '15 at 19:14

Dudi Konfino

1,126
2
13
24

score 0 · Answer 3 · answered Apr 27 '15 at 06:22

may be you can do like this also

declare @t table (name varchar(30))
insert into @t (name)values ('cpc > cpc1 > organic'),('cpc > organic1 >cpc')
select 
SUBSTRING(name,0,CHARINDEX('>',name))As Col1,
SUBSTRING(name, charindex('>', name)+1, len(name) - CHARINDEX('>', reverse(name)) - charindex('>', name))As Col2,
REVERSE(SUBSTRING(reverse(name),0,CHARINDEX('>',reverse(name))))As Col3
 from @t

Split into different columns SQL Server

3 Answers3