using PARSENAME to find the last item in a list

Question

I am using Parsename in SQL and would like to extract the last element in a list of items. I am using the following code.

Declare @string as varchar(1000)
set @string = '25.26.27.28'

SELECT PARSENAME(@string, 1)

This works and returns the value 28 as I expect. However if I expand my list past more than 4 items then the result returns a NULL. For example:

Declare @string2 as varchar(1000)
set @string2 = '25.26.27.28.29'

SELECT PARSENAME(@string2, 1)

I would expect this to return a value of 29 however only NULL is returned

I'm sure there is a simple explaination to this can anyone help?

Solution 1

PARSENAME is designed specifically to parse an sql object name. The number of periods in the latter example exempt it from being such a name so the call correctly fails.

Instead

select right(@string2, charindex('.', reverse(@string2), 1) - 1)

Solution 2

PARSENAME ( 'object_name' , object_piece )

'object_name' Is the name of the object for which to retrieve the specified object part. This name can have four parts: the server name, the database name, the owner name, and the object name.

If we give more than 4 parts, it will always return null.

For Ref: http://msdn.microsoft.com/en-us/library/ms188006.aspx

Comments

[JsonStatham]

What does adding reverse and the -1) actually do?

[Alex K.]

"1x2x3" if you want to find the last x reverse the string to "3x2x1" and get the 1st x with charindex (there is no lastcharindex) then read that offset from the end of the original string, -1 to skip the x itself.