Parsing a section of a pipe-delimited string
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.