Extract string between the pattern1 and pattern2 in sql server -

data in column(c1):
some_datadata1/by_user/some_other_other_data1/somedate1
some_datadata2/by_user/some_other_other_data2/somedate2
some_datadata3/by_user/some_other_other_data3/somedate3
some_datadata4/by_user/some_other_other_data4/somedate4

output required:
some_other_data1
some_other_data2
some_other_data3
some_other_data4

i looking query so

i tried following did not work:

select col1,        substring(col1,charindex('by user/',col1)+len('by user/'),          charindex('/',col1)-charindex('by user/',col1)-len('by user/'))  tablename 

it better split strings in application code.

however, if have no choice, can use left since pattern1 first 1 on left of string:

select col1, left(col1, charindex('by_user/',col1) -2) (values ('some_datadata1/by_user/some_other_other_data1/somedate1')     , ('some_datadata2/by_user/some_other_other_data2/somedate2')     , ('some_datadata3/by_user/some_other_other_data3/somedate3')     , ('some_datadata4/by_user/some_other_other_data4/somedate4')) string(col1) 

if want pattern 3, second charindex need start looking / after position of second pattern:

select col1, left(col1, charindex('by_user/',col1) -2)     , substring(col1, charindex('by_user/',col1) + len('by_user/'),          charindex('/',col1, charindex('by_user/',col1) + len('by_user/'))          - charindex('by_user/',col1) - len('by_user/')         ) (values ('some_datadata1/by_user/some_other_other_data1/somedate1')     , ('some_datadata2/by_user/some_other_other_data2/somedate2')     , ('some_datadata3/by_user/some_other_other_data3/somedate3')     , ('some_datadata4/by_user/some_other_other_data4/somedate4')) string(col1)