Tutorial :Find position of delimited character in a String (SQL Server)



Question:

I have a string Variable

test=HARIA|123|MALE|STUDENT|HOUSEWIFE

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.

any help appreciated


Solution:1

Untested

SELECT      SUBSTRING (         @test,         CHARINDEX('|', @test, CHARINDEX('|', @test) + 1) + 1,         CHARINDEX('|', @test, CHARINDEX('|', @test, CHARINDEX('|', @test) + 1) + 1) - 1      )  

A nicer way would be split the string into a table, and use ROW_NUMBER() to extract the 3rd element. Based on this Arrays and Lists in SQL Server

DECLARE @test varchar(100) = 'HARIA|123|MALE|STUDENT|HOUSEWIFE'    SELECT TOP 8000      Num  INTO      #Number  FROM      (      SELECT         ROW_NUMBER() OVER (ORDER BY c1.object_id) AS Num      FROM         sys.columns c1, sys.columns c2, sys.columns c3      ) N    SELECT      ROW_NUMBER() OVER (ORDER BY Num) AS Rank,      LTRIM(RTRIM(SUBSTRING(@test,                            Num,                            CHARINDEX('|', @test + '|', Num) - Num                  ))) AS Value  FROM      #Number  WHERE      Num <= LEN (@test)      AND      SUBSTRING('|' + @test, Num, 1) = '|'    DROP TABLE #Number  


Solution:2

Try this

Solution 1:(Using a number table)

declare @str varchar(1000)  set @str ='HARIA|123|MALE|STUDENT|HOUSEWIFE'  --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(      @str      ,(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  

Solution 2:(Using XQuery)

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)  SET @str='HARIA|123|MALE|STUDENT|HOUSEWIFE'  SET @xml = cast(('<X>'+replace(@str,'|' ,'</X><X>')+'</X>') as xml)  SELECT ShrededData as DesiredResult FROM(  SELECT   ROW_NUMBER() over(order by getdate()) rn  ,N.value('.', 'varchar(10)') as ShrededData FROM @xml.nodes('X') as T(N))X  WHERE X.rn = 3 -- Specifying the destination sequence value(here 3)  

Output(in both the cases)

DesiredResult  MALE  


Solution:3

I found this. Using a t-Sql for loop. Good reference of syntax, too.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »