The number of ways to receive information seems to increase all the time. One of the ways to get information from the web is an RSS feed. If you use a feed reader.
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 ANSI_NULLS ON
SET NOCOUNT ON
GOCREATE 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:
— 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 instance, 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.