Search string pattern using sql

In my previous post i explained, how to Parsing delimited string using sql using  SQL and asp net

Now in this article i will explain how to Search the string pattern on entire string using the sql. some time we wanted to search the specific string along with the position then we can think of this way to retrieve the string.

Below i have created some sample query which will retrieve the string on specific position 

create table #TempSQL(csvtext varchar(2000) not null)
insert #TempSQL select 'tempa,tempb,tempc,tempd,tempe,tempf,tempg' union select 'temp1,temp,temp3,temp4,temp5,temp6'

select
       dbo.fnGetCsvPart(csvtext,0,default) as pos0
       ,dbo.fnGetCsvPart(csvtext,2,default) as pos2
       ,dbo.fnGetCsvPart(csvtext,2,1) as Entire_string
from #TempSQL

 

Here is the generated output from above query.

pos0                       pos2                          Entire_String
------------------ ------------------------------ ------------------------------
temp1                        temp3                   temp3,temp4,temp5,temp6
tempa                        tempc                   tempc,tempd,tempe,tempf,tempg 

Now create this function

create  function dbo.fnGetCsvPart(@csvtext varchar(2000),@indexPos tinyint, @lastPos bit = 0)
returns varchar(5000)
as

begin
   declare @ivar int; set @ivar = 0
   while 1 = 1
       begin
           if @indexPos = 0
             begin
                 if @lastPos = 1 or charindex('_',@csvtext,@ivar +1) = 0
                       return substring(@csvtext,@ivar +1,len(@csvtext)-@ivar +1)
                     else
                       return substring(@csvtext,@ivar +1,charindex('_',@csvtext,@ivar +1)-@ivar -1)
            end
          select @indexPos = @indexPos-1, @ivar = charindex('_',@csvtext,@ivar +1)
          if @ivar = 0 break
   end
 return null
end
GO

You may call that directly on your query. check below  example.

select Isnull(dbo.fnGetCsvPart(Document_Name,2,default),'') as [Request]