Instantly share code, notes, and snippets.

Embed
What would you like to do?
SELECT
a.uuid
, a.lists
, SUBSTRING(a.lists, 1, ISNULL(p1 - 1, LEN(a.lists))) list1
, SUBSTRING(a.lists, p1 + 1, ISNULL(p2, LEN(a.lists) + 1) - p1 - 1) list2
, SUBSTRING(a.lists, p2 + 1, ISNULL(p3, LEN(a.lists) + 1) - p2 - 1) list3
, SUBSTRING(a.lists, p3 + 1, ISNULL(p4, LEN(a.lists) + 1) - p3 - 1) list4
, SUBSTRING(a.lists, p4 + 1, ISNULL(p5, LEN(a.lists) + 1) - p4 - 1) list5
, SUBSTRING(a.lists, p5 + 1, ISNULL(p6, LEN(a.lists) + 1) - p5 - 1) list6
, SUBSTRING(a.lists, p6 + 1, ISNULL(p7, LEN(a.lists) + 1) - p6 - 1) list7
from SubscriberListMembership a
CROSS APPLY (select NULLIF(CHARINDEX('|', a.lists), 0)) b(p1)
CROSS APPLY (select NULLIF(CHARINDEX('|', a.lists, p1 + 1), 0)) c(p2)
CROSS APPLY (select NULLIF(CHARINDEX('|', a.lists, p2 + 1), 0)) d(p3)
CROSS APPLY (select NULLIF(CHARINDEX('|', a.lists, p3 + 1), 0)) e(p4)
CROSS APPLY (select NULLIF(CHARINDEX('|', a.lists, p4 + 1), 0)) f(p5)
CROSS APPLY (select NULLIF(CHARINDEX('|', a.lists, p5 + 1), 0)) g(p6)
CROSS APPLY (select NULLIF(CHARINDEX('|', a.lists, p6 + 1), 0)) h(p7)
where isnull(a.lists,'') != ''
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment