2

I have a pipe-delimited string that ends with a |. I need the information between the second last | and the last |.

Example:

a|b|c|d|e|f|g|h|ijk|

I need to return ijk. The string will always have 9 pipes if that helps.

0
4

Call me insane, but this is what I came up with:

CREATE TABLE #SomeTable (
    id INT, 
    content VARCHAR(MAX))

INSERT INTO #SomeTable VALUES
    (1, 'a|b|c|d|e|f|Aloha|'),
    (2, 'a|b|c|d|e|f|From|'),
    (3, 'a|b|c|d|e|f|Hawaii|')

SELECT 
    REVERSE(SUBSTRING(REVERSE(LEFT(content, LEN(content)-1)), 0,CHARINDEX('|',REVERSE(left(content, LEN(content)-1)),0)))
FROM #SomeTable

Explanation (from the inside out):

  • Strip the last pipe (|)
  • Reverse the whole string
  • Find the next pipe (ex last)
  • Extract the characters up until the next pipe.
  • Reverse the string again.

Here's another take:

SELECT 
    REPLACE(REVERSE(SUBSTRING(REVERSE(content), 0, CHARINDEX('|',REVERSE(content),2))),'|','')
FROM #SomeTable
  • Reverse the string.
  • Get the position of the next pipe (start looking from the second position to skip the first (ex last) pipe.
  • Reverse the string.
  • Replace the last pipe.
1
  • @JoeObbish please, feel free to edit, I would be more than flattered as I already tried and didn't worked... hahah – Nelson Jun 7 '17 at 3:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.