title: SQL Split String to letters each one in row
body: <p>I have case to get String from table , Split it to letters , each letter represent "active/inactive" "0/1" , then return description from other table for just all active letter IN ONE CELL ..
  NOTE: Letters order as it is in other table ..</p>
  
  <p>this is my case :</p>
  
  <pre><code>CREATE TABLE Strings_tab
  (
     Str_id     NUMBER,
     Str_text   VARCHAR2 (40)
  );
  
  CREATE TABLE Reprsnt_Tab
  (
     rep_id     NUMBER,
     rep_text   VARCHAR2 (40)
  );
  
  INSERT INTO Strings_tab VALUES (1, '1111111111000000000011111111110000000000');
  INSERT INTO Strings_tab VALUES (2, '0000011111000001111100000111110000011111');
  
  INSERT INTO Reprsnt_Tab VALUES (1, 'rep1');
  INSERT INTO Reprsnt_Tab VALUES (2, 'rep2');
  INSERT INTO Reprsnt_Tab VALUES (3, 'rep3');   
  INSERT INTO Reprsnt_Tab VALUES (4, 'rep4');
  INSERT INTO Reprsnt_Tab VALUES (5, 'rep5');
  INSERT INTO Reprsnt_Tab VALUES (6, 'rep6');
  INSERT INTO Reprsnt_Tab VALUES (7, 'rep7');
  INSERT INTO Reprsnt_Tab VALUES (8, 'rep8');
  INSERT INTO Reprsnt_Tab VALUES (9, 'rep9');
  INSERT INTO Reprsnt_Tab VALUES (10, 'rep10');
  INSERT INTO Reprsnt_Tab VALUES (11, 'rep11');
  INSERT INTO Reprsnt_Tab VALUES (12, 'rep12');
  INSERT INTO Reprsnt_Tab VALUES (13, 'rep13');
  INSERT INTO Reprsnt_Tab VALUES (14, 'rep14');
  INSERT INTO Reprsnt_Tab VALUES (15, 'rep15');
  INSERT INTO Reprsnt_Tab VALUES (16, 'rep16');
  INSERT INTO Reprsnt_Tab VALUES (17, 'rep17');
  INSERT INTO Reprsnt_Tab VALUES (18, 'rep18');
  INSERT INTO Reprsnt_Tab VALUES (19, 'rep19');
  INSERT INTO Reprsnt_Tab VALUES (20, 'rep20');
  INSERT INTO Reprsnt_Tab VALUES (21, 'rep21');
  INSERT INTO Reprsnt_Tab VALUES (22, 'rep22');
  INSERT INTO Reprsnt_Tab VALUES (23, 'rep23'); 
  INSERT INTO Reprsnt_Tab VALUES (24, 'rep24');
  INSERT INTO Reprsnt_Tab VALUES (25, 'rep25');
  INSERT INTO Reprsnt_Tab VALUES (26, 'rep26');
  INSERT INTO Reprsnt_Tab VALUES (27, 'rep27');
  INSERT INTO Reprsnt_Tab VALUES (28, 'rep28');
  INSERT INTO Reprsnt_Tab VALUES (29, 'rep29');
  INSERT INTO Reprsnt_Tab VALUES (30, 'rep30');
  INSERT INTO Reprsnt_Tab VALUES (31, 'rep31');
  INSERT INTO Reprsnt_Tab VALUES (32, 'rep32');
  INSERT INTO Reprsnt_Tab VALUES (33, 'rep33');
  INSERT INTO Reprsnt_Tab VALUES (34, 'rep34');
  INSERT INTO Reprsnt_Tab VALUES (35, 'rep35');
  INSERT INTO Reprsnt_Tab VALUES (36, 'rep36');
  INSERT INTO Reprsnt_Tab VALUES (37, 'rep37');
  INSERT INTO Reprsnt_Tab VALUES (38, 'rep38');
  INSERT INTO Reprsnt_Tab VALUES (39, 'rep39');  
  INSERT INTO Reprsnt_Tab VALUES (40, 'rep40');
  
  COMMIT;
  </code></pre>
  
  <p>this is my query :</p>
  
  <pre><code>SELECT STR_TEXT,
         RTRIM (
            XMLAGG (XMLELEMENT (E, DATA.REP_TEXT || ',' || CHR (10))).EXTRACT (
               '//text()'),
            ',')
            REPS
    FROM (  SELECT LETTER,
                   STR_ID,
                   LVL,
                   STR_TEXT,
                   REP_TEXT
              FROM (    SELECT DISTINCT SUBSTR (A.STR_TEXT, LEVEL, 1) LETTER,
                                        A.STR_ID,
                                        LEVEL LVL,
                                        A.STR_TEXT
                          FROM STRINGS_TAB A
                    CONNECT BY LEVEL &lt;= LENGTH (A.STR_TEXT) ---- HERE IS MY PROBLEM
                                          ) TXT,
                   (  SELECT ROWNUM RN, REPRSNT_TAB.*
                        FROM REPRSNT_TAB
                    ORDER BY REP_ID) B
             WHERE B.RN = TXT.LVL AND LETTER = 1
          ORDER BY STR_ID, STR_TEXT, LVL) DATA
          GROUP BY STR_TEXT
  </code></pre>
  
  <p>This query get correct data 
  If i put like 10 in place of "LENGTH (A.STR_TEXT)" to get first 10 letters of each string.
  BUT .. If "LENGTH (A.STR_TEXT)" was so big , in my case 40 , the query will hang 
  so, please , advice me .. in this case ???</p>
  
  <p>RESULT from my Query , IF I put "LEVEL &lt;= 10" :</p>
  
  <pre><code>       STR_TEXT                         REP
  ------------------------------- ------------------------------
  000001111100000111110000011111  rep6,rep10,rep9,rep8,rep7
  111111111100000000001111111111  rep1,rep10,rep9,rep8,rep7,rep6,rep5,rep4,rep3,rep2
  </code></pre>