tags:

views:

740

answers:

2

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().

+1  A: 

You could replace the '/' with a '.' and use PARSENAME.

Here's a SO answer using it: Split String in SQL

Mitch Wheat
I didn't even know about `PARSENAME()`. Though it seems it can only handle up to four individual parts.
Tomalak
hey but it won't work properly if there are any other dots (".") in the string. and in this case there are. -1
agnieszka
@agnieszka: true, but I assumed that was obvious.
Mitch Wheat
ok but it's still a bad solution to my problem
agnieszka
+3  A: 
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
Tomalak
+1. It's a good answer; I still like PARSENAME better! ;)
Mitch Wheat