x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

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 ]

Feedback

Gravatar

# re: Mimic Oracles INSTR Function

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

# 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
Gravatar

# 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
Gravatar

# re: Mimic Oracles INSTR Function

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

# 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
Gravatar

# 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
Gravatar

# re: Mimic Oracles INSTR Function

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

# re: Mimic Oracles INSTR Function

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

# 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

http://images.google.com/images?q=Sari&hl=en&lr=&sa=N&tab=ii&oi=imagest



2/4/2005 8:55 AM | Brett
Gravatar

# 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
Gravatar

# table variable?

Do you mean to build a table variable everytime?

Isn't that more overhead?

And How would Rownumber help?

2/4/2005 2:57 PM | Brett
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET