How to get last part of nvarchar with variable size in T-SQL?

Question

Imagine that I have the following value in my nvarchar variable:

DECLARE @txt nvarchar(255)
SET @txt = '32|foo|foo2|123'

Is there a way to easily get the last part just after the last | that is 123 in this case ?

I could write a split function but I'm not interested in the first parts of this string. Is there another way to get that last part of the string without getting the first parts ?

Note that all parts of my string have variable sizes.

Answer 1

You can use a combination of LEFT, REVERSE and CHARINDEX for this. The query below reverses the string, finds the first occurance of |, strips out other characters and then straightens the string back.

DECLARE @txt nvarchar(255)
SET @txt = '32|foo|foo2|123'

SELECT REVERSE(LEFT(REVERSE(@txt),CHARINDEX('|',REVERSE(@txt))-1))

Output

123

Edit

If your string only has 4 parts or less and . isn't a valid character, you can also use PARSENAME for this.

DECLARE @txt nvarchar(255)
SET @txt = '32|foo|foo2|123'
SELECT PARSENAME(REPLACE(@txt,'|','.'),1)