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().
views:
740answers:
2
+1
A:
You could replace the '/' with a '.' and use PARSENAME.
Here's a SO answer using it: Split String in SQL
Mitch Wheat
2009-08-08 10:28:15
I didn't even know about `PARSENAME()`. Though it seems it can only handle up to four individual parts.
Tomalak
2009-08-08 13:41:08
hey but it won't work properly if there are any other dots (".") in the string. and in this case there are. -1
agnieszka
2009-08-09 21:11:24
@agnieszka: true, but I assumed that was obvious.
Mitch Wheat
2009-08-10 03:23:50
ok but it's still a bad solution to my problem
agnieszka
2009-08-10 07:25:05
+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
2009-08-08 10:34:25