Split single column into two columns by separating colon

Question

I want the output by splitting one column having colon in between to two separated columns.One condition is if there is no colon then the value should be in second new column

SingleColumn
------------ 
TYPE: HIGH POWER/MAGNETIC
LAMPTYPE: FLUORESCENT
BALLAST STYLE

NewColumn1    NewColumn2
----------    ----------
Type          HIGH POWER/MAGNETIC
LAMPTYPE      FLUORESCENT
              BALLAST STYLE

There are a LOT of "split string" solutions out there. There are MANY answers to this question here on StackOverflow, as well as on the internet in general. This seems like a duplicate question to me, and should probably be closed. https://stackoverflow.com/questions/10914576/t-sql-split-string — pmbAustin, Jun 19 '19 at 20:33
Wha are you *actually* trying to do? If you want to store a bunch of data that doesn't need to be filtered store it as XML or, starting with SQL Server 2016, JSON. Storing *this* type of data is wrong if you need to query it, as it breaks even the 1st Normal Form. Don't do it. Splitting the data the way you want is worse, as now you have no idea what you'll find in the new columns — Panagiotis Kanavos, Jun 21 '19 at 10:23
BTW why should `BALLAST STYLE` appear in the *second* column? Why should it be treated as a value of the *previous* attribute instead of an attribute namedf `BALLAST` with a value of `STYLE` ? *This* format needs an actual parser with backtracking, not just splits and substrings — Panagiotis Kanavos, Jun 21 '19 at 10:25

score 1 · Answer 1 · edited Jun 19 '19 at 20:41

Perhaps use substring functions to extract the parts you want:

select
    iif(
        charindex(':', SingleColumn) > 0,
        left(SingleColumn, charindex(':', SingleColumn) - 1),
        null
    ) as NewColumn1,

    iif(
        charindex(':', SingleColumn) > 0,
        right(SingleColumn, len(SingleColumn) - charindex(':', SingleColumn)),
        SingleColumn
    ) as NewColumn2
 from my_table

If the string doesn't contain the delimiter character then the second column gets the entire value.

benjamin moskovits · Answer 2 · 2019-06-19T21:20:01.550

0

If you are using SQL Server 2016 and on you can use the new string_split function:

SELECT colid,prod1,prod2 
FROM my_table  
CROSS APPLY STRING_SPLIT(singlecolumn, ':');

where colid is a regular column and prod1 and prod2 will contain the two parts. If there is one (no :) then prod1 will contain the value.

edited Jun 19 '19 at 21:20

answered Jun 19 '19 at 20:08

benjamin moskovits

5,261
1
12
22

That's wrong. There is no `;` in the string, and there is no prod1, prod2, and even more it won't give the desired results, because there is no separator before `LAMPTYPE: `. – Ilyes Jun 19 '19 at 20:44

Split single column into two columns by separating colon

2 Answers2