Welcome to C l e a r G l a s s

Source Code management and database deployment
VSS Tips & Tricks
Moving and copying files via File System Object

I’ve found this awesome string parsing function for SQL written by Nick Barclay from Australia. The function returns the character position of the character after the Nth instance of the search string.

SQL Code:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO
 
CREATE FUNCTION InString(
 @string varchar(200),
 @searchfor varchar(50),
 @position int
) returns int
 
AS
BEGIN
DECLARE @lenstring int, @poscount int, @stringpos int
SET @lenstring = datalength(@searchfor)
SET @poscount = 1
SET @stringpos = 1
WHILE @poscount < = @position and @stringpos <= len(@string)
 – string segment found
 BEGIN
  IF substring(@string, @stringpos, @lenstring)=@searchfor
  BEGIN
   – is the one we are looking for?
   IF @poscount = @position
    BEGIN
     SET @stringpos = @stringpos + @lenstring
     return @stringpos
    END
   ELSE – look for the next instance
    BEGIN
     SET @poscount = @poscount + 1
    END
  END
  SET @stringpos = @stringpos + 1
 END
 return null
END
GO
 
GRANT EXEC on dbo.InString TO PUBLIC
GO

Example 1:

Returns 18
select dbo.InString('123456 123456 123456 123456', '23', 3)

Example 2:

Returns 25
select dbo.InString('test1/test2/test3/test4/test5/', '/', 4)

Example 3:

Returns ‘test4’
declare @teststring varchar(50)
set @teststring = ‘test1/test2/test3/test4/test5/’
select substring(@teststring, dbo.Instring(@teststring,’/',3),5)

Example 4:

Returns ‘textxyz’
(variable length delimited fields
declare @teststring2 varchar(50)
set @teststring2 = ‘test123/test/testtestestest/testxyz/test/’
select substring( @teststring2,
dbo.Instring(@teststring2, ‘/’, 3),
(dbo.Instring(@teststring2, ‘/’, 4) -1)
- dbo.Instring(@teststring2, ‘/’, 3)
)