InString / INSTR Function In Microsoft SQL

This one seems to of made the rounds on the internet, no matter it’s been great use to me. Here’s where I found it: INSTR Function in MSSQL

I modified their version because theirs was returning the character location AFTER the searched for character. Since that’s not what normal InString() functions do and since there is no guarantee to be a character after the searched for character I changed it to return the actual position of the character searched for.

Enjoy!

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET NOCOUNT ON
GO
drop function InString
go
CREATE function InString(

@string varchar(200),
@searchfor varchar(50),
@position int
) returns int

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

— TEST CASE #1
— should return 17
select dbo.InString(‘123456 123456 123456 123456′, ’23’, 3)
as [Test Case #1]

— TEST CASE #2
— should return 24
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)+1,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)+1,
(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 = 0
while @poscount <= @position and @stringpos <= len(@string)
begin
— if we find the string segment we’re looking for
if substring(@string, @stringpos+1, @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

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: