# x002548's Blog

Not Just a Number - Brett Kaiser

## Mimic Oracles INSTR Function

Starting at a user defined location in a string to find the nth occurance of a target string's starting location

EDIT: OK, I got Jay's set based method to work.  And I mistook Jay for Jay White (Page47) but I was wrong (but I should be given a pass, because it's just like what Mr. 47 would have written).  Now we have to ask, Jay who?

Not anymore, thanks Jay

Anyway Thanks Jay.  Just as a note:  This requires a numbers table.  The concept of using this to eliminate recursion and cursors is sort of akin to turning a light on after stumbling around in the dark for so long (and even stumbling around in the light, since I couldn't employee this concept in my original shot at this).  The reasons to not use set based processing is becoming smaller and smaller on a daily basis.  Anyway, enjoy.

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE numbers(n int)
GO

DECLARE @x int
SET @x = 1

WHILE @x < 1001
BEGIN
INSERT INTO numbers(n) SELECT @x
SELECT @x = @x + 1
END
GO

CREATE FUNCTION udf_Instr
(@str1 varchar(8000), @str2 varchar(8000), @start int, @Occurs int)
RETURNS int
AS
BEGIN
DECLARE @instr int
SELECT TOP 1  @instr = instr
FROM (
SELECT   d.*
, (SELECT COUNT(1)
FROM (SELECT DISTINCT CHARINDEX(@str1,SUBSTRING(@str2,@start,LEN(@str2)-@start+1),n) AS instr
FROM numbers
WHERE n <= LEN(@str2))AS d2
WHERE d2.instr <= d.instr) AS ct

FROM (SELECT DISTINCT CHARINDEX(@str1,SUBSTRING(@str2,@start,LEN(@str2)-@start+1),n) AS instr
FROM numbers
WHERE n <= LEN(@str2)) AS d
) AS d
GROUP BY instr,ct
HAVING ct <= @occurs
ORDER BY ct DESC
RETURN @instr
END

GO

SELECT dbo.udf_Instr('x','axbxcxdx',1,4)
GO

My Original shot:

CREATE FUNCTION udf_Instr
(@str1 varchar(8000), @str2 varchar(8000), @start int, @Occurs int)
RETURNS int
AS
BEGIN
DECLARE @Found int, @LastPosition int
SET @Found = 0
SET @LastPosition = @start - 1

WHILE (@Found < @Occurs)
BEGIN
IF (CHARINDEX(@str1, @str2, @LastPosition + 1) = 0)
BREAK
ELSE
BEGIN
SET @LastPosition = CHARINDEX(@str1, @str2, @LastPosition + 1)
SET @Found = @Found + 1
END
END

RETURN @LastPosition
END
GO

SELECT dbo.udf_Instr('x','axbxcxdx',1,4)
GO

DROP FUNCTION udf_Instr
GO

Print | posted on Wednesday, February 02, 2005 1:03 PM | Filed Under [ SQL Server ]

## #re: Mimic Oracles INSTR Function

Terrific looping technique!
-Semper fi
2/2/2005 4:43 PM | Xerxes

## #Some Loops Can't be avoided

You do what you can. Just try and avoid Cursors at all costs....

Unless you're using Oracle, then you have no choice for cetrain operations....besides, and I'll have to research this, I believe theu operate totally differently internally..

That would make a good article I guess.

2/2/2005 5:53 PM | Brett

## #re: Mimic Oracles INSTR Function

Hi Brett,

Here is a set-based method which perform the same functionality.
I think... :)

[code]
select top 1 instr
from
(
select d.*,
(
select count(1) from
(
select distinct charindex(@str1,@str2,n) instr
from numbers
where n <= len(@str2)
) d2 where d2.instr <= d.instr
) as ct

from
(
select distinct charindex(@str1,@str2,n) instr
from numbers
where n <= len(@str2)
) d
) d
group by instr,ct
having ct <= @occurs
order by ct desc
[/code]
2/2/2005 11:01 PM | Jay

## #re: Mimic Oracles INSTR Function

Well that did not format properly at all :(
2/2/2005 11:02 PM | Jay

## #No code tags

Sorry, no code tags here...After I get a chance to underatnd it, I'll redit the post, turn it in to a function and format it...and supply a cut and pasteable code snipet.

Thanks Jay

But what happened to the @start parameter?

2/3/2005 9:22 AM | Brett

## #re: Mimic Oracles INSTR Function

Oops, I guess I missed that one.. Just append it to where criterias making them.

BETWEEN (@start and LEN(@str2)

I did not test it all that much, just a few conditions. Give it a shake down and see if it works the same.
2/3/2005 9:47 AM | Jay

## #re: Mimic Oracles INSTR Function

Me thinks Jay is ehorn from the forums.
2/3/2005 2:15 PM | Tara

## #re: Mimic Oracles INSTR Function

Yea, its me. Sorry for the confusion. :)
2/3/2005 2:40 PM | Jay (aka ehorn)

## #Sorry?

You take the time to give us code like that...

No Sari's needed.

Thanks again!

EDIT: Damn can't make a nice hyperlink

2/4/2005 8:55 AM | Brett

## #re: Mimic Oracles INSTR Function

I think the set-based solution looks nice, but you might instead want to use a table variable with an identity column, for efficiency's sake. In SS2005 the ROW_NUMBER function will do away with the need for that, luckily!
2/4/2005 10:50 AM | Adam Machanic

## #table variable?

Do you mean to build a table variable everytime?