declare @result varchar(50)
set @result = 'ssf/sdf/sdf/sf/sf/sf/10/2009'

select replace(@result,'/2009','')
/*ssf/sdf/sdf/sf/sf/sf/10*/
select reverse(replace(@result,'/2009',''))
/*01/fs/fs/fs/fds/fds/fss*/
select patindex('%/%',reverse(replace(@result,'/2009','')))
/*3*/
select left(reverse(replace(@result,'/2009','')),(patindex('%/%',reverse(replace(@result,'/2009',''))))-1)
/*01*/
select reverse (left(reverse(replace(@result,'/2009','')),(patindex('%/%',reverse(replace(@result,'/2009',''))))-1))
/*10*/

SELECT 
    SUBSTRING(data, LEN(data) - CHARINDEX('/', REVERSE(data), CHARINDEX('/', REVERSE(data)) + 1) + 2, 
    CHARINDEX('/', REVERSE(data), CHARINDEX('/', REVERSE(data)) + 1) - CHARINDEX('/', REVERSE(data)) - 1)
FROM (
    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'
) AS test_data

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;

SELECT  
    CASE WHEN data NOT LIKE '%/%/%' THEN '' ELSE
    SUBSTRING(data, LEN(data) - CHARINDEX('/', REVERSE(data), CHARINDEX('/', REVERSE(data)) + 1) + 2,  
    CHARINDEX('/', REVERSE(data), CHARINDEX('/', REVERSE(data)) + 1) - CHARINDEX('/', REVERSE(data)) - 1) END
FROM ( 
    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 '' UNION ALL
    SELECT '2009' 
) AS test_data