Find position of delimited character in a String

Posted by Niladri.Biswas under Sql Server category on | Points: 40 | Views : 1129
I have a string Variable


i am using | as delimited character. if i want to extract data from 2nd occurence of pipe till 3rd occurrence. i need to get 'MALE' from above string.

declare @str varchar(1000)
--Creating a number table
;with numcte as(
select 1 as rn union all select rn+1 from numcte where rn<LEN(@str)),
--Get the position of the "|" charecters
GetDelimitedCharPos as(
select ROW_NUMBER() over(order by getdate()) seq, rn,delimitedcharpos
from numcte
cross apply(select SUBSTRING(@str,rn,1)delimitedcharpos) X where delimitedcharpos = '|')

--Applying the formula SUBSTRING(@str,startseq + 1,endseq-startseq + 1)
-- i.e. SUBSTRING(@str,11,15-11) in this case

select top 1 SUBSTRING(
,(select top 1 rn+1 from GetDelimitedCharPos where seq =2)
,(select top 1 rn from GetDelimitedCharPos where seq =3) -
(select top 1 rn+1 from GetDelimitedCharPos where seq =2)
) DesiredResult
from GetDelimitedCharPos

Comments or Responses

Login to post response