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

Transact-SQL
1

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

2

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

3

Hi.
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
4

hi

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 
  from 
     #data a 
	   cross apply 
    string_split(a.strng,','))
select * from cte where rn =2

image

5

hi

hope this helps

another way = using XML ..much simpler

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

image