question

BLT avatar image
BLT asked

Extract data between pipes

I am trying extract the digits between the 18th and 19th pipe of a string. The string will always have varying data but will always contain the same number of pipes. Here's a sample: PID|||796650||Hulk^Incredible^Lee||19470625|M|||593 SARATOGA CT^^CHASKA^^^USA||()368-4151|(000)000-0000||||700102633|
string
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
So, basically you are looking for that last set of numbers correct? Assuming so, would this work for you? DECLARE @text VARCHAR(500) = 'PID|||796650||Hulk^Incredible^Lee||19470625|M|||593 SARATOGA CT^^CHASKA^^^USA||()368-4151|(000)000-0000||||700102633|' SELECT REVERSE(LEFT(REVERSE(LEFT(@text, LEN(@text)-1)), CHARINDEX('|',REVERSE(LEFT(@text, LEN(@text)-1)))-1)) Hope that helps!
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hello John, I'm sorry I should have stated that are more pipes and data following the 19th pipe. It is not always a set number of pipes, which makes it difficult to use reverse. My thought was to start at "PID" which always exists and count the number of pipes somehow, but have really struggled to figure out a solution. Thanks for helping.
0 Likes 0 ·
sdoubleday avatar image
sdoubleday answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you SDoubleday! With a few tweaks, this is now working beautifully! Have a great Tuesday!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.