Find Character Nth Occurrence in a String

  DECLARE @string NVARCHAR(255)= '\Root\Node1\Node2\Node3\Node4'

--Option1 Recursive
 ;with mycte as

 Select 1 as i, stuff(@string,1, charindex('\',@string),'') s

 union all

 Select i+1 as i, stuff(s,1, charindex('\',s),'') s from mycte 
 where charindex('\',s)>0

 select s from mycte
 WHERE i=3

 --Option2 Cross apply
 ;with mycte as (
  select @string s
 select @string, Stuff(@string, 1, d3.n ,'') as newString  from mycte 
  cross apply (select (charindex('\',@string))) as d1(n)
  cross apply (select (charindex('\',@string, d1.n+1))) as d2(n)
  cross apply (select charindex('\',@string, d2.n+1)) as d3(n)


  ---Option 3 charindex, patindex and stuff
select Stuff(@string,1,charindex('\',@string,patindex('%\%\%',Stuff(@string,1,1,''))+2),'')


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s