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),'')


 
Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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