SQL: find character position in string value

Question

I have a column with values ​​below the sample

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

I want to show the position "Y" through the select SQL 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;

Request Result:

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

The query does not work for the 3rd and 4th rows. How to fix it? Why doesn't it work?

Answer 1

Your request has invalid syntax because it contains two sentences FROM, one of which does not have a corresponding sentence SELECT.

It also has:

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

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

, , Y, .

:

SQL Fiddle

Oracle 11g R2:

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

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

:

|                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) |