How to split concatenated field in SQL with SSIS or SQL

Question

I have problem that I have to split a concatenated field into different rows. The delimiter is a "+" marker. So in my field I have 3%+2%+1% and what I want is row 1 ->3%, row 2 -> 2% and so on. But there is one more big problem: I don't know how many concatenated values we have so it could 3, 5 or maybe 10 values.

Can somebody help me solving this issue with SSIS or SQL.

Here is an example of a `split()` function that does what you are looking for: http://stackoverflow.com/questions/2507330/sql-server-split-operation. — Gordon Linoff, Jun 16 '14 at 11:26
My favourite article on the matter is - [Split strings the right way – or the next best way](http://sqlperformance.com/2012/07/t-sql-queries/split-strings). This does only apply to SQL Server though. There are however hundreds, if not thousands of similar questions on Stackoverflow, what specific DBMS are you using? — GarethD, Jun 16 '14 at 11:29
@GarethD: the question is about ssis, so i assume that this is about SQL Server. THe splitting is not a problem and there is a lot of question about splitting. I see that the problem is with unknow numer of columns. Question to author: are you sure that you want all of this field in your destination table? Can you find the maximum numer of columns? Are you sure that you can not pivot this columns into rows? It;s really hard to maintain something like this and you will have a lot of troubles: from loading the data up to performance. — sdrzymala, Jun 16 '14 at 12:06
@sdrzymala This would be a fair assumption, however, you can connect to other DBMS using SSIS, so before closing the question as a duplicate of a question aimed at SQL-Server, I thought it only fair to request confirmation from the OP before closing. Another option is to extract the data using an SSIS script task, then impliment `string.Split()` to turn the delimited data into rows. It really depends on what the final output needs to be. — GarethD, Jun 16 '14 at 12:13
@GarethD I'm Reading data from a SQL DB and Importing it into another SQL DB. Ps can you give more information about the second comment you made with the Script function. Because I think that is thing I need — Phoenix, Jun 16 '14 at 13:20

score 0 · Answer 1 · edited May 23 '17 at 12:05

For me @sdrzymala is correct here. I would normalise this data first before loading it to a database. If the client or report needed the data pivoted or denormalised again I would do this in client code.

1) First I would save the following split function and staging table "PercentsNormalised" into the database. I got the split function from this question here.

-- DDL Code:
Create FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);
GO

Create Table PercentsNormalised(
    RowIndex Int,
    -- Other fields here,
    PercentValue Varchar(100)
)
GO

2) Either writing some SQL (like below) or using the same logic in a SSIS dataflow task transform the data like so and insert into the "PercentsNormalised" table created above.

With TestData As (

  -- Replace with "real table" containing concatenated rows
  Select '3%+2%+1%' As Percents Union All
  Select '5%+1%+1%+0%' Union All
  Select '10%+8%' Union All
  Select '10%+5%+1%+1%+0%'

),

TestDataWithRowIndex As (

  -- You might want to order the rows by another field 
  -- in the "real table"
  Select Row_Number() Over (Order By Percents) As RowIndex,
      Percents
  From TestData
)

-- You could remove this insert and select and have the logic in a 
-- SSIS Dataflow task
Insert PercentsNormalised
    Select td.RowIndex,
        ss.Item As PercentValue    
    From TestDataWithRowIndex As td
        Cross Apply dbo.SplitStrings(td.Percents, '+') ss;

3) Write client code on the "PercentsNormalised" table using say the SQL pivot operator.

How to split concatenated field in SQL with SSIS or SQL

1 Answers1