Finding the Character position of nth occurence in a string.

Hi Friends:

I usually run this query in Oracle to find out the position of a character in a string from first position to the nth occurence.

For example, I run the following queries in oracle to get the desire result.

SQL> select instr('DADADAQQQA','A',1,1) FROM DUAL;

INSTR('DADADAQQQA','A',1,1) -- First occurence of 'A' from start.
---------------------------
2

SQL> select instr('DADADAQQQA','A',1,2) FROM DUAL;

INSTR('DADADAQQQA','A',1,2) -- Second occurence of 'A' from start.
---------------------------
4

SQL> select instr('DADADAQQQA','A',1,3) FROM DUAL;

INSTR('DADADAQQQA','A',1,3) -- Third occurence of 'A' from start.
---------------------------
6

SQL> select instr('DADADAQQQA','A',1,4) FROM DUAL;

INSTR('DADADAQQQA','A',1,4) -- Forth occurence of 'A' from start.
---------------------------
10


Is there ay equivelant way in Transact- SQL? If not, can anybody suggest the solution?


--Raj

Look in Books online under CHARINDEX. That'll help you.

------------
Raj at 6/15/01 11:11:14 AM

Hi Friends:

I usually run this query in Oracle to find out the position of a character in a string from first position to the nth occurence.

For example, I run the following queries in oracle to get the desire result.

SQL> select instr('DADADAQQQA','A',1,1) FROM DUAL;

INSTR('DADADAQQQA','A',1,1) -- First occurence of 'A' from start.
---------------------------
2

SQL> select instr('DADADAQQQA','A',1,2) FROM DUAL;

INSTR('DADADAQQQA','A',1,2) -- Second occurence of 'A' from start.
---------------------------
4

SQL> select instr('DADADAQQQA','A',1,3) FROM DUAL;

INSTR('DADADAQQQA','A',1,3) -- Third occurence of 'A' from start.
---------------------------
6

SQL> select instr('DADADAQQQA','A',1,4) FROM DUAL;

INSTR('DADADAQQQA','A',1,4) -- Forth occurence of 'A' from start.
---------------------------
10


Is there ay equivelant way in Transact- SQL? If not, can anybody suggest the solution?


--Raj

Hi Adam:

CHARINDEX doesn't satisfy the below results as it gives only the first occurence.

Please suggest some other way or if you feel that it can be be done thru CHARINDEX then please give some example.


--Raj


------------
Raj at 6/15/01 11:11:14 AM

Hi Friends:

I usually run this query in Oracle to find out the position of a character in a string from first position to the nth occurence.

For example, I run the following queries in oracle to get the desire result.

SQL> select instr('DADADAQQQA','A',1,1) FROM DUAL;

INSTR('DADADAQQQA','A',1,1) -- First occurence of 'A' from start.
---------------------------
2

SQL> select instr('DADADAQQQA','A',1,2) FROM DUAL;

INSTR('DADADAQQQA','A',1,2) -- Second occurence of 'A' from start.
---------------------------
4

SQL> select instr('DADADAQQQA','A',1,3) FROM DUAL;

INSTR('DADADAQQQA','A',1,3) -- Third occurence of 'A' from start.
---------------------------
6

SQL> select instr('DADADAQQQA','A',1,4) FROM DUAL;

INSTR('DADADAQQQA','A',1,4) -- Forth occurence of 'A' from start.
---------------------------
10


Is there ay equivelant way in Transact- SQL? If not, can anybody suggest the solution?


--Raj

Nothing off the top of my head. You might want to programatically solve your problem using len, substring, and charindex together. The actual pseudocode involves declaring a variable designating the order of occurance. Then write some algorithm to find that occurance using the functions above.

good luck.

------------
Raj at 6/15/01 11:23:22 AM

Hi Adam:

CHARINDEX doesn't satisfy the below results as it gives only the first occurence.

Please suggest some other way or if you feel that it can be be done thru CHARINDEX then please give some example.


--Raj


------------
Raj at 6/15/01 11:11:14 AM

Hi Friends:

I usually run this query in Oracle to find out the position of a character in a string from first position to the nth occurence.

For example, I run the following queries in oracle to get the desire result.

SQL> select instr('DADADAQQQA','A',1,1) FROM DUAL;

INSTR('DADADAQQQA','A',1,1) -- First occurence of 'A' from start.
---------------------------
2

SQL> select instr('DADADAQQQA','A',1,2) FROM DUAL;

INSTR('DADADAQQQA','A',1,2) -- Second occurence of 'A' from start.
---------------------------
4

SQL> select instr('DADADAQQQA','A',1,3) FROM DUAL;

INSTR('DADADAQQQA','A',1,3) -- Third occurence of 'A' from start.
---------------------------
6

SQL> select instr('DADADAQQQA','A',1,4) FROM DUAL;

INSTR('DADADAQQQA','A',1,4) -- Forth occurence of 'A' from start.
---------------------------
10


Is there ay equivelant way in Transact- SQL? If not, can anybody suggest the solution?


--Raj

Hi Adam:

Thanks for your suggestion.

Now, I can do it.

--Raj

------------
adam at 6/15/01 1:02:58 PM


Nothing off the top of my head. You might want to programatically solve your problem using len, substring, and charindex together. The actual pseudocode involves declaring a variable designating the order of occurance. Then write some algorithm to find that occurance using the functions above.

good luck.

------------
Raj at 6/15/01 11:23:22 AM

Hi Adam:

CHARINDEX doesn't satisfy the below results as it gives only the first occurence.

Please suggest some other way or if you feel that it can be be done thru CHARINDEX then please give some example.


--Raj


------------
Raj at 6/15/01 11:11:14 AM

Hi Friends:

I usually run this query in Oracle to find out the position of a character in a string from first position to the nth occurence.

For example, I run the following queries in oracle to get the desire result.

SQL> select instr('DADADAQQQA','A',1,1) FROM DUAL;

INSTR('DADADAQQQA','A',1,1) -- First occurence of 'A' from start.
---------------------------
2

SQL> select instr('DADADAQQQA','A',1,2) FROM DUAL;

INSTR('DADADAQQQA','A',1,2) -- Second occurence of 'A' from start.
---------------------------
4

SQL> select instr('DADADAQQQA','A',1,3) FROM DUAL;

INSTR('DADADAQQQA','A',1,3) -- Third occurence of 'A' from start.
---------------------------
6

SQL> select instr('DADADAQQQA','A',1,4) FROM DUAL;

INSTR('DADADAQQQA','A',1,4) -- Forth occurence of 'A' from start.
---------------------------
10


Is there ay equivelant way in Transact- SQL? If not, can anybody suggest the solution?


--Raj