How to locate the nth character within a string? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to locate the nth character within a string?

Author

Topic

dbads10
Starting Member

4 Posts

Posted - 2011-08-02 : 07:45:47

Hello,

How can I locate the nth character within a string column? Ex: Find the location of the third "D" in the column.
I tried using CHARINDEX, but I was unable to use wild cards like %.
Any ideas?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 07:51:39

you can use PATINDEX if you want to serach for patterns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dbads10
Starting Member

4 Posts

Posted - 2011-08-02 : 08:01:54

Thanks visakh16
But PATINDEX returns the location of the first occurance, not the fifth or sixth occurance location...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-02 : 09:24:31

I can't think of a clever way to use PATINDEX or any of the other string functions to do this for an arbitrary N. If you have a table of numbers, you can do it via a query like this:


-- create table of numbers
CREATE TABLE #Numbers(n INT NOT NULL PRIMARY KEY CLUSTERED);
WITH N(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < 100) INSERT INTO #Numbers SELECT * FROM N;

-- string to search
DECLARE @x VARCHAR(32); SET @x = 'abcdefabcdd';
-- occurrence number
DECLARE @n INT; SET @n = 2;
-- charcter to search
DECLARE @c char; SET @c = 'a';

SELECT
   n
FROM
   (
       SELECT
          n,
          ROW_NUMBER() OVER(ORDER BY n) RN
       FROM
          #Numbers
       WHERE
          n <= LEN(@x)
          AND SUBSTRING(@x, n, 1) = @c
   ) A
WHERE
   a.RN = @n;

If you need to use it against a table, it may even be possible to make it into a function, but not sure how efficient that would be though.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-08-02 : 09:25:22

Also, long, old thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-02 : 10:47:49

I can't come up with a practical application of finding the nth occurence... why do you want to do this?

Corey

I Has Returned!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-02 : 11:17:08

[code]
DECLARE @str varchar(100) = 'abcadefaghiajkl',
@ch char = 'a',
@n int = 3

; with rcte as
(
select n = 1, p = charindex(@ch, @str)

union all

select n = n + 1, p = charindex(@ch, @str, r.p + 1)
from rcte r
where charindex(@ch, @str, r.p + 1) > 0
and r.n < @n
)
select *
from rcte
where n = @n
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources