How can I get the almost last substring from "/" delimited string in T-SQL?

Question

If I've got a string that consists of other strings delimited with "/" character (xxx...xxx/xxx/xxxx) how can I get the last and the almost last (the one before last) part with t-sql? It should probably be some combination of charindex() and right().

Answer 1

declare @s varchar(50);
set @s = 'aaaaa/bbbbb/ccccc/ddddd/eeeee'

/* last one: */
select
    RIGHT(@s, CHARINDEX('/', REVERSE(@s)) - 1)

/* penultimate one */
select
    RIGHT(
      LEFT(@s, LEN(@s) - CHARINDEX('/', REVERSE(@s))), 
      CHARINDEX('/', REVERSE(
        LEFT(@s, LEN(@s) - CHARINDEX('/', REVERSE(@s)))
      )) - 1
    )

The "last one" is pretty straightforward, no explanation needed.

The "penultimate one" is essentially equal to the "last one", with all occurrences of @s replaced with:

LEFT(@s, LEN(@s) - CHARINDEX('/', REVERSE(@s)))

which produces 'aaaaa/bbbbb/ccccc/ddddd'

To check whether there are enough slashes in the string for this expression to succeed, you could do

CASE WHEN LEN(@s) - LEN(REPLACE(@s, '/', '')) >= 2 
     THEN /* expression here */
     ELSE /* error value here */
END