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]
