how to return characters between two /'s 1

[darwin101]

hi all

I am trying to return the characters between 2 /
where the field contains " /abc/defgh " i want 'abc'
i am able to remove the first/ but not the second and the characters to the left.
substring(field,charindex('/',field)+1 ,len(field))

any pointers would be appreciated

 

[SQLDenis]

declare @v varchar(50)
select @v=' /abc/defgh '

select left(substring(@v,charindex('/',@v)+1 ,len(@v)),
charindex('/',substring(@v,charindex('/',@v)+1 ,len(@v)))-1)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[darwin101]

thanks that works great.

is it possiable to make this work if there is only one / in the field?
where @v = '/abcd '

 

[darwin101]

got it, Thanks Denis
select case
when path like '/%/%' then
left(substring(path,charindex('/',path)+1 ,len(path)),charindex('/',substring(path,charindex('/',path)+1 ,len(path)))-1)
else substring(path,2,30)
end

from catalog