The intended usage is a bit questionable to me as well. In fact I have used it for the intended purposes exactly never. It is however useful. You can leverage it as a very limited string splitter. I have used it to parse emails into multiple components. You can do something similar for URLs or other strings you need to parse. The limitation is a max number of 4 elements.
Here is an example.
declare @Email varchar(100) = ‘email@example.com’
–First we replace any existing periods with a character sequence not found in (any reasonable) email address because PARSENAME uses the period as the delimiter
SET @Email = replace(replace(@Email, ‘.’, ‘^%^’), ‘@’, ‘.’)
–We need to undo the replace to restablish the original periods
select UserName = replace(PARSENAME(@Email, 2), ‘^%^’, ‘.’)
, DomainName = replace(PARSENAME(@Email, 1), ‘^%^’, ‘.’)