How to extract text between first and second instance of text string

Looking for support on how to extract text between the first and second instance of comma in text string.

I want to remove everything before Johnson and after Biopsy based on commas.

Text String = Contract,Johnson Biopsy,Y,Filter
Output = Johnson Biopsy

DECLARE @string varchar(200)

SET @string = 'Contract,Johnson Biopsy,Y,Filter'

SET @string = SUBSTRING(@string, CHARINDEX(',', @string + ',') + 1, 8000)
SET @string = LEFT(@string, CHARINDEX(',', @string + ',') - 1)
SELECT @string

Another way could be this:

declare @text varchar(max)='Contract,Johnson Biopsy,Y,Filter'
;with cte0 as
(select @text as t, charindex(',', @text) + 1 as p0)
,cte as(select t, p0, charindex(',', @text, p0) as p1 from cte0)
select substring(t,p0, p1-p0) from cte


hope this helps

another way to do this using string split

i have create a table with identity column and a bunch of strings

create data script

drop table #data
create table #data (id int identity(1,1),strng varchar(300))
insert into #data select 'contract,johnson biopsy,y,filter'
insert into #data select 'xx,yy,ok,sample'
insert into #data select 'movie,ben stiller,dont know,yes'

;with cte as 
( select 
   row_number() over(partition by id order by id ) as rn 
   , id
   , value 
     #data a 
	   cross apply 
select * from cte where rn =2



hope this helps

another way = using XML ..much simpler

    CAST('<x>' + REPLACE(strng,',','</x><x>') + '</x>' AS XML).value('/x[2]','varchar(max)')
FROM #data