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
To get the number of times a particular string occurs in another string
DECLARE @STR VARCHAR(100) = 'ABCDEABCDEABCDEABCDEABCDEABCDEABCDEABCDEABCDE'
DECLARE @SEARCH VARCHAR(10) = 'A'
-- TO GET THE NUMBER OF OCCURRENCE COUNT
SELECT (LEN(@STR) - LEN(REPLACE(@STR,@SEARCH,'')))/LEN(@SEARCH) OCCURRENCECOUNT
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