April 12, 2011 at 8:24 pm
Comments posted to this topic are about the item Extend CHARINDEX with occurance matching
April 13, 2011 at 7:04 am
This code seems to be overly complicated for the task presented. Why are you saving off the Chopped part of the input? If I needed this functionality I would keep track of the current position and use something like:
CREATE FUNCTION [dbo].[ufnGetCharIndexWithOccurance](@ToSearch VARCHAR(max), @InSearch VARCHAR(max), @Occurance INT=1)
RETURNS int
AS
--SELECT dbo.ufnGetCharIndexWithOccurance('AB', 'ABDABRCTAB', 5)
BEGIN
DECLARE @LoopCounter integer
DECLARE @CurrentLocation integer
DECLARE @result integer
SET @LoopCounter = 1
SET @result = 0
While @LoopCounter <= @Occurance BEGIN
Set @result = CHARINDEX(@ToSearch, @InSearch, @result+1)
IF @result = 0 --Occurance Not found
Set @LoopCounter = @Occurance + 1
ELSE
Set @LoopCounter = @LoopCounter + 1
END
RETURN @result
END
Note: I don't validate @Occurance. If @Occurance is not a valid input the While Condition will fail and the function will return 0.
--
JimFive
April 13, 2011 at 7:22 am
Way too complicated!
No need for any while loops just a good old Tally (Numbers) table:
DECLARE @search VARCHAR(20)
DECLARE @find VARCHAR(2)
SELECT @search = '-ABCDABFGHABXYZ.', @find = 'AB'
SELECT
occurence = ROW_NUMBER() OVER (ORDER BY n),
position = n ,
SUBSTRING(@search,n,LEN(@find))
FROM
dbo.Tally
WHERE
N < LEN(@search)
AND
SUBSTRING(@search,n,LEN(@find)) = @find
See the link in my sig for tally table info
April 13, 2011 at 7:25 am
Ok, I thought about this a bit more and I don't like the loop. So I came up with the following that requires a Tally Table (A table of integers)
CREATE FUNCTION udfCharIndexPositionTally(@ToSearch varchar(max), @InSearch VARCHAR(max), @Occurance INT)
-- SELECT udfCharIndexPositionTally('AB','ABDABRCTAB',2) AS
RETURNS INT
DECLARE @return integer
SELECT @return = position
FROM (SELECT Row_Number() OVER(ORDER BY n) as RowNum
, n as position, SUBSTRING(@InSearch, n,Len(@ToSearch)) as compare
FROM TALLY
WHERE n>0 AND n <=Len(@InSearch)
AND SubString(@InSearch, n, Len(@ToSearch)) = @ToSearch) t
WHERE RowNum = @Occurance
RETURN @return
--
JimFive
April 13, 2011 at 7:29 am
James Goodwin (4/13/2011)
Ok, I thought about this a bit more and I don't like the loop. So I came up with the following that requires a Tally Table (A table of integers)
Ditto!
April 27, 2011 at 3:38 am
DECLARE @Numbertable table
(
ID INT PRIMARY KEY
)
INSERT INTO @Numbertable
select TOP 1000 ROW_NUMBER() OVER(order by si.object_id)
from sys.objects si,sys.objects s
DECLARE @separatolog TABLE
(
SeparatorLogID INT,
ID INT IDENTITY(1,1) PRIMARY KEY
)
DECLARE @STR varchar(100) = 'ABCDEABCDEABCDEABCDEABCDEABCDEABCDEABCDEABCDE'
DECLARE @Separator VARCHAR(10) = ''
DECLARE @ValueAdd VARCHAR(10) = 'DEA'
INSERT INTO @separatolog
select distinct CHARINDEX(@ValueAdd,@str,ID)
from @Numbertable
where ID <= LEN(@str) and CHARINDEX(@ValueAdd,@str,ID) > 0
ORDER BY 1
select ID AS 'OCCURENCE',SeparatorLogID AS 'Exact Location' from @separatolog
Regards,
Mitesh OSwal
+918698619998
July 14, 2011 at 3:28 am
May 16, 2016 at 7:20 am
Thanks for the script.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy