Using a common table expression you can also do neat tricks. I've interpreted your task as "return the next-to-last word from each of the /-separated list of words". In these string manipulations you have to be very careful with the substring() function: passing it a negative value for the index will result in very-hard-to-debug runtime errors. I think this should deal correctly with all sorts of input:

declare @tbl table (

data nvarchar(max) null

);

insert @tbl(data)

SELECT 'dat1/dat2/dat3/dat4/1/2009' AS data UNION ALL

SELECT 'dat1/dat2/dat34/dat4/2/2009' UNION ALL

SELECT 'dat1/dat2/dat/sd34/dat4/3/2009' UNION ALL

SELECT 'ssf/sdf/sdf/sf/sf/sf/4/2009' UNION ALL

SELECT 'ssf/sdf/sdf/sf/sf/sf/10/2009' UNION ALL

SELECT 'ssf/sdf/sdf/sf/sdf/sf/sf/44444444/2009' UNION ALL

SELECT 'ssf/sdf/sdf/sf//sdf sdf sf/sf/111/2009' UNION ALL

SELECT 'ssf/sdf/sdf/sf/sf/sd sf sdf /1798/2009' UNION ALL

SELECT '2009' union all

select null;

with cteWords as (

select

1 as nr,

reverse(case when charindex('/', t.data) > 0

then left(t.data, charindex('/', t.data) - 1)

else t.data

end) as word,

case when charindex('/', t.data) > 0

then RIGHT(t.data, len(t.data) - charindex('/', t.data))

else CONVERT(nvarchar(max),null)

end as remainder

from @tbl tbl

cross apply (

select REVERSE(tbl.data) as data

) t

union all

select

c.nr + 1 as nr,

reverse(case when charindex('/', c.remainder) > 0

then left(c.remainder, charindex('/', c.remainder) - 1)

else c.remainder

end) as word,

case when charindex('/', c.remainder) > 0

then right(c.remainder, len(c.remainder) - charindex('/', c.remainder))

else null

end as remainder

from cteWords c

where not c.remainder is null

)

select word

from cteWords

where nr = 2;