Improve SQL String Parsing

The number of ways to receive information seems to increase all the time. One of the new ways to get information from the web is an RSS feed. If you use a feed reader, you may be interested in subscribing to the the Novick Software RSS feed. It contains notices about all the new pages on the site, including the online version of this newsletter. You'll find the feed at:
http://www.novicksoftware.com/rss.xml

This issue features a contribution from reader Nick Barclay from Australia. The function, InString, is one he's found useful for parsing strings into their parts. The function returns the character position of the character after the Nth instance of the search string. It's useful for parsing delimited strings. Here's the CREATE FUNCTION script:

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

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Returns the position of the character AFTER the nth instance 
of the string

-- TEST CASE #1
-- should return 18
select dbo.InString('123456 123456 123456 123456', '23', 3) 
                    as [Test Case #1]

-- TEST CASE #2
-- should return 25
select dbo.InString('test1/test2/test3/test4/test5/', '/', 4) 
                     as [Test Case #2]

-- TEST CASE #3
declare @teststring varchar(50)
set @teststring = 'test1/test2/test3/test4/test5/'
select substring(@teststring, dbo.Instring(@teststring,'/',3),5)
                     as [Test Case #3]
-- should return 'test4'

-- TEST CASE #4 (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)
			) as [Test Case #4]
-- should return 'testxyz'
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

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) 
	begin
		-- if we find the string segment we're looking for 
        if substring(@string, @stringpos, @lenstring)=@searchfor
			begin
                -- is the instance of the string the one we are
                --  looking for?
				if @poscount = @position 
					begin
                        set @stringpos = @stringpos + @lenstring
						return @stringpos
					end
                -- else look for the next instance of the string
                -- segment
				else 
					begin
						set @poscount = @poscount + 1
					end
			end
		set @stringpos = @stringpos + 1
	end
return null
end
GO

GRANT EXEC on dbo.InString TO PUBLIC 
GO

The tests from the function header illustrate how the function works so lets use them as the demonstration:

-- TEST CASE #1
-- should return 18
select dbo.InString('123456 123456 123456 123456', '23', 3) 
                    as [Test Case #1]

-- TEST CASE #2
-- should return 25
select dbo.InString('test1/test2/test3/test4/test5/', '/', 4) 
                     as [Test Case #2]

-- TEST CASE #3
declare @teststring varchar(50)
set @teststring = 'test1/test2/test3/test4/test5/'
select substring(@teststring, dbo.Instring(@teststring,'/',3),5)
                     as [Test Case #3]
-- should return 'test4'

-- TEST CASE #4 (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)
			) as [Test Case #4]
-- should return 'testxyz'
GO


(Results)
Test Case #1 
------------ 
18

Test Case #2 
------------ 
25

Test Case #3 
------------ 
test4

Test Case #4                                       
-------------------------------------------------- 
testxyz

Test cases 3 and 4 really illustrate how to use the function in a real situation. Take a closer look at test 3:

In this case we have strings that are five characters separated by backslash (/). The expression returns the string after the 3rd backslash.

Thanks to Nick for his contribution.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick