How to identify the position of the nth occurance of character in a string
-
Hi Folks,
I would like to know the best way to identify the nth occurrance of a specified character in a string?
e.g: lets say I have this string aaaddddfffggghhhhjjjj, I want to know the best way to find the position in the string of the second f character (highlighted)? Then I want to take this position as the starting position for a substring() operation.
Any ideas?
Thanks,
Morrislgn
-
Morris Logan (7/30/2008)
Hi Folks,I would like to know the best way to identify the nth occurrance of a specified character in a string?
e.g: lets say I have this string aaaddddfffggghhhhjjjj, I want to know the best way to find the position in the string of the second f character (highlighted)? Then I want to take this position as the starting position for a substring() operation.
Any ideas?
Thanks,
Morrislgn
The string operations are rather limited in T-SQL. You can find the nth occurrence with a function like http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.server/2004-10/2535.html.
If you want to do something more complex, and you need to do it frequently, you may also want to evaluate CLR procedures.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
You can use a numbers/tally table
http://www.sqlservercentral.com/articles/TSQL/62867/
For example
DECLARE @Parameter VARCHAR(100)DECLARE @ch CHAR(1)
SET @Parameter='aaaddddfffggghhhhjjjj'
SET @ch='f'
SELECT ROW_NUMBER() OVER(ORDER BY N) AS nth,
N AS [Position In String]
FROM dbo.Tally
WHERE N <= LEN(@Parameter) AND SUBSTRING(@Parameter,N,1) = @ch
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
HI all,
A Good idea would be to change the function supplied to use the Tally table instead of a loop.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
Hello
If you're prepared to experiment a little, then a numbers or tally table can make this kind of operation really sing. Here's something which might start you off.
DECLARE @cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000), @nOccurrence INT
SET @cSearchExpression = 'f'
SET @cExpressionSearched = 'aaaddddfffggghhhhjjjj'
SET @nOccurrence = 2
SELECT CHARINDEX(@cSearchExpression, @cExpressionSearched, n.number) AS StartPos
FROM dbo.Numbers n
WHERE n.number <= LEN(@cExpressionSearched)
GROUP BY CHARINDEX(@cSearchExpression, @cExpressionSearched, n.number)
HAVING CHARINDEX(@cSearchExpression, @cExpressionSearched, n.number) > 0
ORDER BY 1
Results:
89
10
Here's a recipe for a tally table (I think this one's courtesy of Jeff Moden)
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Numbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)DROP TABLE dbo.Numbers
--===== Create and populate the Tally table on the fly
SELECT TOP 1000000
IDENTITY(int,1,1) AS number
INTO dbo.Numbers
FROM master.dbo.syscolumns sc1,
master.dbo.syscolumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_numbers_number PRIMARY KEY CLUSTERED (number)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Numbers TO PUBLIC
Cheers
ChrisM
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - ChrisM@Work - Wednesday, July 30, 2008 3:45 AM
Nothing much was needed, just modified a little and I can now get correct occurance, if your account is still active then you can modify your answer to make it complete. Thanks for a nice script.
My additions are highlighted in yellow.
SELECT TOP 1 * FROM (
SELECT TOP (@nOccurrence) CHARINDEX(@cSearchExpression, @cExpressionSearched, n.number) AS StartPos
FROM dbo.Numbers n
WHERE n.number <= LEN(@cExpressionSearched)
AND (LEN(@cExpressionSearched) - LEN(REPLACE(@cExpressionSearched, @cSearchExpression,''))) >= @nOccurrence
GROUP BY CHARINDEX(@cSearchExpression, @cExpressionSearched, n.number)
HAVING CHARINDEX(@cSearchExpression, @cExpressionSearched, n.number) > 0
ORDER BY 1
) a
ORDER BY a.StartPos DESC - @awais_487
It was an interesting exercise at the time 🙂
Mark Cowne's solution is far more efficient. If you wish to explore further, here are both queries set up in a little test harness:DECLARE @cSearchExpression VARCHAR(4000), @cExpressionSearched VARCHAR(4000), @nOccurrence INT
SET @cSearchExpression = 'f'
SET @cExpressionSearched = 'aaaddddfffggghhhhjjjj'
SET @nOccurrence = 2
;WITH Numbers (number) AS (
SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d1 (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d2 (n)
)
SELECT *
FROM (
SELECT
Occurrence = ROW_NUMBER() OVER (ORDER BY n.number),
Position = n.number
FROM Numbers n
WHERE SUBSTRING(@cExpressionSearched,n.number,LEN(@cSearchExpression)) = @cSearchExpression
) d WHERE Occurrence = @nOccurrence
;WITH Numbers (number) AS (
SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d1 (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d2 (n)
)
SELECT TOP 1 *
FROM (
SELECT TOP(@nOccurrence)
CHARINDEX(@cSearchExpression, @cExpressionSearched, n.number) AS StartPos
FROM Numbers n
WHERE n.number <= LEN(@cExpressionSearched)
AND (LEN(@cExpressionSearched) - LEN(REPLACE(@cExpressionSearched, @cSearchExpression,''))) >= @nOccurrence
GROUP BY CHARINDEX(@cSearchExpression, @cExpressionSearched, n.number)
HAVING CHARINDEX(@cSearchExpression, @cExpressionSearched, n.number) > 0
ORDER BY 1
) a
ORDER BY a.StartPos DESC“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
@SSC-Dedicated
Thanks, this makes more sense 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply