Clever Geek Handbook

How do I get the last part of a variable sized nvarchar in T-SQL?

Imagine I have the following value in my variable nvarchar

:

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

Is there a way to easily get the last part right after the last |

, i.e. 123

in this case?

I could write a function split

, but I am not interested in the first parts of this line. Is there any other way to get that last part of the string without getting the first parts?

Please note that all parts of my string are variable in size.

+3


source to share


2 answers


You can use a combination LEFT

, REVERSE

and CHARINDEX

for this purpose. The query below reverses the string, finds the first occurrence |

, removes 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 contains 4 parts or less and is .

not a valid character, you can also use PARSENAME

for that.

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


source


You can change the line to get the desired result:



DECLARE @txt nvarchar(255) = '32|foo|foo2|123'
SELECT REVERSE(SUBSTRING(REVERSE(@txt), 1, CHARINDEX('|', REVERSE(@txt)) -1))
+2


source






More articles:


All Articles