SQL split in one column into two columns based on delimiter(s)

Question

My data in one column(col) is as follows: Col

Accounts::Changes
Applications::Zen::Other
Server::Access

I need this data to go to two columns. I want the first string before the delimiter (:) to go into one column (col1) and the last string after the last delimiter to go into another column (col2).

The output should be:
Col1                Col2
Accounts            Changes
Applications        Zen
Server              Access

I am using sql server 2008 R2

possible duplicate of [How to split a comma-separated value to columns](http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — Elias, Jul 15 '14 at 00:49

score 3 · Answer 1 · answered Jul 15 '14 at 00:45

3

You should be able to do this with basic string operations:

select left(col, charindex('::', col) - 1) as col1,
       right(col, charindex('::', reverse(col)) - 1) as col2
from table t;

Here is a SQL Fiddle.

answered Jul 15 '14 at 00:45

Gordon Linoff

1,242,037
58
646
786

wouldn't the second line of code give the last string after the last delimiter? I need to get the first and second strings and ignore the rest of them. – svon Jul 15 '14 at 04:17
@svon . . . According to your sample data, you want the first and last values. – Gordon Linoff Jul 15 '14 at 11:11
Sorry, I will edit the question. I want the first and second strings – svon Jul 15 '14 at 17:11

score 0 · Answer 2 · answered Jul 15 '14 at 19:06

I have been able to achieve this as follows:

select A.Col1,
 case when CHARINDEX('::',A.Colx)>0 
         then SUBSTRING(A.Colx,1,CHARINDEX('::',A.Colx)-1) 
         else A.Colx end Col2, 
    CASE WHEN CHARINDEX('::',A.Colx)>0 
         THEN SUBSTRING(A.Colx,CHARINDEX('::',A.Colx)+2,len(A.Colx))  
         ELSE NULL END as Colx3
FROM ( 
 select 
    case when CHARINDEX('::',Col)>0 
         then SUBSTRING(Col,1,CHARINDEX('::',Col)-1) 
         else Col end Col1, 
    CASE WHEN CHARINDEX('::',Col)>0 
         THEN SUBSTRING(Col,CHARINDEX('::',Col)+2,len(Col))  
         ELSE NULL END as Colx
FROM Table1 ) as A

Although I end up getting a third column with the leftover string, I won't use it.

SQL split in one column into two columns based on delimiter(s)

2 Answers2