question

Hello BLT, a recursive common table expression (CTE) ([ https://technet.microsoft.com/en-US/library/ms186243(v=SQL.105).aspx][1]) might be what you are looking for. Please see sample code below: /*Disclaimer - this may perform quite badly as the number of elements in your initial string rises. Test this code before using it in production.*/ /*The MyData common table expression is standing in for your table of values. I incremented the final value, and added some ragged endings to simulate the inconsistent length of the rows you described.*/ ;with MyData AS ( SELECT * FROM ( VALUES ('PID|||796650||Hulk^Incredible^Lee||19470625|M|||593 SARATOGA CT^^CHASKA^^^USA||()368-4151|(000)000-0000||||700102633|MeaninglessAddition|') ,('PID|||796650||Hulk^Incredible^Lee||19470625|M|||593 SARATOGA CT^^CHASKA^^^USA||()368-4151|(000)000-0000||||700102634|MeaninglessAddition2|RaggedEdge|') ,('PID|||796650||Hulk^Incredible^Lee||19470625|M|||593 SARATOGA CT^^CHASKA^^^USA||()368-4151|(000)000-0000||||700102635|MeaninglessAdditionWithoutFinalPipe') ,('PID|||796650||Hulk^Incredible^Lee||19470625|M|||593 SARATOGA CT^^CHASKA^^^USA||()368-4151|(000)000-0000||||700102636|') ,('PID|||796650||Hulk^Incredible^Lee||19470625|M|||593 SARATOGA CT^^CHASKA^^^USA||()368-4151|(000)000-0000||||700102637|') ) AS d (data) ) /*We shred the data in a recursive common table expression: https://technet.microsoft.com/en-US/library/ms186243(v=SQL.105).aspx */ ,ShreddedData AS ( /*Anchor member*/ SELECT SUBSTRING(MyTarget.data, 0, CHARINDEX('|', MyTarget.data)) AS ExtractedElement ,SUBSTRING(MyTarget.data, CHARINDEX('|', MyTarget.data) + 1 , LEN(MyTarget.data) - CHARINDEX('|', MyTarget.data)) AS data ,1 AS ElementNumber ,ROW_NUMBER() OVER (ORDER BY MyTarget.Data /*Arbitrary*/) AS OriginalRecordIdentifier FROM MyData /*Clean up the inputs - if we do not have a final Pipe character, this will run forever. The use of CROSS APPLY is not necessary, but means I only have to use this CASE statement once.*/ CROSS APPLY ( SELECT CASE WHEN CHARINDEX('|', REVERSE(MyData.data)) > 1 THEN MyData.data + '|' ELSE MyData.data END AS data ) AS MyTarget UNION ALL /*Recursive member*/ SELECT SUBSTRING(MyTarget.data, 0, CASE WHEN CHARINDEX('|', MyTarget.data) = 0 THEN LEN(MyTarget.Data) ELSE CHARINDEX('|', MyTarget.data) END ) AS ExtractedElement ,SUBSTRING(MyTarget.data, CHARINDEX('|', MyTarget.data) + 1 , LEN(MyTarget.data) - CHARINDEX('|', MyTarget.data)) AS data ,MyTarget.ElementNumber + 1 /*Note the incrementation*/ ,OriginalRecordIdentifier /*Note that this is the same for the recursive members originating from the same anchor member. This is how we know which elements go together.*/ FROM ShreddedData AS MyTarget /*Defining when to stop*/ WHERE LEN(MyTarget.data ) > 0 ) /*Look at all the elements with this: */ --SELECT * FROM ShreddedData /*If we presume that you will need multiple elements from the list, you need to know what the order of elements within the string, but we can aggregate those elements grouped on the arbitrarily assigned OriginalRecordIdentifier*/ Select /*First number after PID -- perhaps a unique identifier?*/ MIN(CASE WHEN ElementNumber = 4 then ExtractedElement else NULL END) AS FourthElement /*Your requested element*/ ,MIN(CASE WHEN ElementNumber = 19 then ExtractedElement else NULL END) AS NineteenthElement FROM ShreddedData GROUP BY OriginalRecordIdentifier ORDER BY OriginalRecordIdentifier /*Unlimited maximum recursion.*/ OPTION (MAXRECURSION 0) [1]: https://technet.microsoft.com/en-US/library/ms186243(v=SQL.105).aspx