# question

## 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|

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

·
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

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 ·