SQL : Find the position of a character in a string value

Question

I have a column with below sample values

MyColumn
----------
NNNNNYYNNNNNYYNNNNNYYNNNNNYYNNN
NNYYNNNNNYYNNNNNYYNNNNNYYNNN
YYNNNNNYYNNNNNYYNNNNNYYNNNNNYY
YYNNNNNYYNNNNNYYNNNNNYYNNNNNYYN

I want to display the position of 'Y' through SQL select statement.

Below is my SQL query.

SELECT LISTAGG(instr(MyColumn, 'Y', 1, level), ' ') WITHIN
 GROUP(
 ORDER BY level)
  FROM dual
CONNECT BY level < instr(MyColumn, 'Y', 1, level) Y_Position from MyTable;

Output of the query is,

Y_Position
------------
6 7 13 14 20 21 27 28
3 4 10 11 17 18 24 25
1
1

The query is not working for 3rd and 4th rows. How to fix this? Why is it not working?

Answer 1

Your query has invalid syntax as it has two FROM clauses one of which does not have a matching SELECT clause.

It also has:

CONNECT BY level < instr(MyColumn, 'Y', 1, level)

Which will not work when the string starts with a Y as LEVEL is 1 and INSTR( 'YYYY', 'Y', 1, 1 ) is 1 and then the filter is CONNECT BY 1 < 1 which is not true. You want to check that CONNECT BY INSTR( MyColumn, 'Y', 1, LEVEL ) > 0.

You also need another filter to check for the case when there are no Y characters as a hierarchical query will always return at least one row.

You can adapt your query to use a correlated sub-query:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE MyTable( MyColumn ) AS
SELECT 'NNNNNYYNNNNNYYNNNNNYYNNNNNYYNNN' FROM DUAL UNION ALL
SELECT 'NNYYNNNNNYYNNNNNYYNNNNNYYNNN' FROM DUAL UNION ALL
SELECT 'YYNNNNNYYNNNNNYYNNNNNYYNNNNNYY' FROM DUAL UNION ALL
SELECT 'YYNNNNNYYNNNNNYYNNNNNYYNNNNNYYN' FROM DUAL UNION ALL
SELECT 'NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN' FROM DUAL

Query 1:

SELECT (
         SELECT LISTAGG( INSTR( t.MyColumn, 'Y', 1, LEVEL ), ' ' )
                  WITHIN GROUP ( ORDER BY LEVEL )
         FROM   DUAL
         WHERE  INSTR( t.MyColumn, 'Y' ) > 0
         CONNECT BY INSTR( t.MyColumn, 'Y', 1, LEVEL ) > 0
       ) AS Y_position
FROM   Mytable t

Results:

|                Y_POSITION |
|---------------------------|
|     6 7 13 14 20 21 27 28 |
|     3 4 10 11 17 18 24 25 |
| 1 2 8 9 15 16 22 23 29 30 |
| 1 2 8 9 15 16 22 23 29 30 |
|                    (null) |