Splitting delimited values in a SQL column into multiple rows

by

If you are on SQL Server 2016+

You can use the new STRING_SPLIT function, which I’ve blogged about here, and Brent Ozar has blogged about here.

SELECT s.[message-id], f.value
  FROM dbo.SourceData AS s
  CROSS APPLY STRING_SPLIT(s.[recipient-address], ';') as f;

If you are still on a version prior to SQL Server 2016

Create a split function. This is just one of many examples out there:

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

I’ve discussed a few others here, here, and a better approach than splitting in the first place here.

Now you can extrapolate simply by:

SELECT s.[message-id], f.Item
  FROM dbo.SourceData AS s
  CROSS APPLY dbo.SplitStrings(s.[recipient-address], ';') as f;

Also I suggest not putting dashes in column names. It means you always have to put them in [square brackets].

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. SET versus SELECT when assigning variables?
  3. How to print VARCHAR(MAX) using Print Statement?
  4. Concatenate values based on ID
  5. Cumulative Total in MS Sql server [duplicate]
  6. T-SQL split string
  7. How can I do an UPDATE statement with JOIN in SQL Server?
  8. How to update two tables in one statement in SQL Server 2005?
  9. How can I get column names from a table in SQL Server?
  10. Conditional WHERE clause in SQL Server
  11. How can I select the first day of a month in SQL?
  12. Multi-statement Table Valued Function vs Inline Table Valued Function
  13. How to return multiple values in one column (T-SQL)?
  14. Select statement to find duplicates on certain fields
  15. SELECT FOR UPDATE with SQL Server
  16. Compare DATETIME and DATE ignoring time portion
  17. EXISTS vs JOIN and use of EXISTS clause
  18. Split function in SQL Server 2008
  19. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  20. How to split string using delimiter char using T-SQL?
  21. OPENXML with xmlns:dt
  22. SQL server select distinct rows using most recent value only
  23. How can I group by date time column without taking time into consideration
  24. Eliminate and reduce overlapping date ranges
  25. SQL Server SELECT INTO @variable?
  26. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  27. How do I find the data directory for a SQL Server instance?
  28. Compare dates in T-SQL, ignoring the time part
  29. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment