I am just starting DBA and curious about how can I split entire column with data like: "1-2/456/£25.00-26.00/56/10+". My delimiter is "/" and I need to get data in 5 columns accordingly. So data to be displayed as: Col01: 1-2; Col02: 456; Col03:£25.00-26.00, Col04:56; Col05: 10+. I tried Jeff Moden, but it splits data to rows, not to columns. I am struggling to link function to use data from column, not just values.

SELECT * FROM [dbo].[DelimitedSplit8K]('Masha/Sasha/Nadia/Loki','/')

So I actually need column instead of 'Masha/Sasha/Nadia/Loki' and convert to columns, not rows.


String splitting functions return the data in rows, because handling dynamic columns is a lot more complex in SQL because that's not really what SQL is supposed to be used for.

To get data in columns, you can use for example pivot or max + case, something like this:

  max(case when ItemNumber = 1 then Item end), 
  max(case when ItemNumber = 2 then Item end), 
  max(case when ItemNumber = 3 then Item end), 
  max(case when ItemNumber = 4 then Item end), 
  max(case when ItemNumber = 5 then Item end)

You could also create a function / SQL that uses charindex + substring to find the places of the separator and just select the 5 pieces directly without creating them into rows.

If you need to handle dynamic amount of columns, then you'll have to create the SQL dynamically and calculate the maximum number of separators in your data first.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.