Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find the Nth Occurrence of a Character in a String Expand / Collapse
Author
Message
Posted Monday, September 24, 2007 1:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 5, 2008 2:49 PM
Points: 57, Visits: 14
Comments posted to this topic are about the item Find the Nth Occurrence of a Character in a String


Post #401865
Posted Wednesday, December 2, 2009 4:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 2, 2009 4:55 AM
Points: 1, Visits: 2
helped me alot, thnx
Post #827329
Posted Wednesday, December 2, 2009 5:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
I suspect this would be more efficient with a numbers/tally table

declare @TargetStr varchar(8000), 
@SearchedStr varchar(8000),
@Occurrence int
set @TargetStr='a'
set @SearchedStr='abbabba'
set @Occurrence=3;

WITH Occurrences AS (
SELECT Number,
ROW_NUMBER() OVER(ORDER BY Number) AS Occurrence
FROM master.dbo.spt_values
WHERE Number BETWEEN 1 AND LEN(@SearchedStr) AND type='P'
AND SUBSTRING(@SearchedStr,Number,LEN(@TargetStr))=@TargetStr)
SELECT Number
FROM Occurrences
WHERE Occurrence=@Occurrence



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #827334
Posted Wednesday, December 1, 2010 10:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 21, 2011 8:35 AM
Points: 1, Visits: 3
Looks wrong, if you go more than 1 above the maximum char number then it will loop through again.

if you change the WHILE statement from
while (@counter < @Occurrence) 

to
while (@counter &lt; @Occurrence) and @pos &lt;&gt; 0

that should fix the issue

Try running below

DECLARE @TXT varchar(255)
SET @TXT = 'Michael Williamson'
SELECT dbo.CHARINDEX2('l',@TXT,1)
SELECT dbo.CHARINDEX2('l',@TXT,2)
SELECT dbo.CHARINDEX2('l',@TXT,4)
SELECT dbo.CHARINDEX2('l',@TXT,6)

Hope that helps
Post #1028802
Posted Thursday, January 20, 2011 4:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 4:02 AM
Points: 1, Visits: 3
Thanks! I found this function really useful. Just adding the middle line here got around the looping issue for me:

set @pos = @ret

if @pos = 0 set @counter = @Occurrence

end
Post #1050620
Posted Thursday, February 6, 2014 12:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 11:42 AM
Points: 1, Visits: 1
Thank you for your fix code! I was banging my head agains the wall to find out why my stored proc was bugging out sometimes - turns out that one of my functions was using this charindex2 without accounting for looping behavior
Post #1538793
Posted Thursday, February 6, 2014 1:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:34 AM
Points: 3,311, Visits: 7,138
This is an old thread but I would suggest a different approach. It might get give better results performance wise.
/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION [dbo].[ProperCase]
(
@TargetStr CHAR(1),
@SearchedStr VARCHAR(8000),
@Occurrence INT = 2
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(N)
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@SearchedStr),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteAppearances(N, rn) AS( SELECT N, ROW_NUMBER() OVER (ORDER BY N) rn
FROM cteTally
WHERE SUBSTRING(@SearchedStr, N, 1) = @TargetStr
)
SELECT TOP 1 CASE WHEN rn >= @Occurrence THEN N ELSE 0 END N
FROM cteAppearances
WHERE rn = @Occurrence OR rn < @Occurrence
ORDER BY N DESC
GO

/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION [dbo].[ProperCase2]
(
@TargetStr VARCHAR(8000),
@SearchedStr VARCHAR(8000),
@Occurrence INT = 2
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(N)
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@SearchedStr),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteAppearances(N, rn) AS( SELECT N, ROW_NUMBER() OVER (ORDER BY N) rn
FROM cteTally
WHERE SUBSTRING(@SearchedStr, N, LEN(@TargetStr)) = @TargetStr
)
SELECT TOP 1 CASE WHEN rn >= @Occurrence THEN N ELSE 0 END N
FROM cteAppearances
WHERE rn = @Occurrence OR rn < @Occurrence
ORDER BY N DESC
GO




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1538817
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse