1

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.

marked as duplicate by Michael Green, Tom V, Paul White sql-server May 29 '16 at 15:16

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

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:

select
  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)
from
  [dbo].[DelimitedSplit8K]('Masha/Sasha/Nadia/Loki','/')

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.

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