sfmc-sql-field-parsing-with-cross-apply.sql

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,'') != ''