title: oracle database: split a string by characters
body: <p>Being almost a newbie on Oracle database I kindly ask for your help.</p>
  
  <p>I need to split a string into different records one for each caracter:</p>
  
  <p>I have a query containing with a VARCHAR field that I need to split into several characters</p>
  
  <pre><code>select
      MAPS.MAP_ID,
      HARD_BIN_LINES.LINE, HARD_BIN_LINES.BINS
  from MAPS, HARD_BIN_LINES
  where MAPS.MAP_ID = 9595435 and MAPS.MAP_ID = HARD_BIN_LINES.MAP_ID 
  order by HARD_BIN_LINES.MAP_ID, HARD_BIN_LINES.LINE
  
  
  MAP_ID  LINE    BINS
  9595435 1       ÿÿÿÿÿÿÿÿÿÿÿþþþþÿÿÿÿÿÿÿÿÿÿÿ
  9595435 2       ÿÿÿÿÿÿÿþþ       þþÿÿÿÿÿÿÿÿ
  9595435 3       ÿÿÿÿÿþþ2           þÿÿÿÿÿÿ
  9595435 4       ÿÿÿÿþ               þþÿÿÿÿ
  9595435 5       ÿÿÿþ2                 þÿÿÿ
  9595435 6       ÿÿþ                    þÿÿ
  9595435 7       ÿÿþ2                   þÿÿ
  9595435 8       ÿþþÿ                   þþÿ
  9595435 9       ÿ2                      þÿ
  9595435 10      þÿ                      þÿ
  9595435 11      þ                        þ
  9595435 12      ü                        þ
  9595435 13      ü2                       þ
  9595435 14      þ                        þ
  9595435 15      þ                        ÿ
  9595435 16      ÿþ             xx      þÿ
  9595435 17      ÿþ                      þÿ
  9595435 18      ÿÿþ                    þÿÿ
  9595435 19      ÿÿþ                    þÿÿ
  9595435 20      ÿÿÿþ                  þÿÿÿ
  9595435 21      ÿÿÿÿþ               þþÿÿÿÿ
  9595435 22      ÿÿÿÿÿþ      þ 2    þÿÿÿÿÿÿ
  9595435 23      ÿÿÿÿÿÿÿþ þ  þþ    ÿÿÿÿÿÿÿÿ
  9595435 24      ÿÿÿÿÿÿÿÿÿÿÿþþþþÿÿÿÿÿÿÿÿÿÿÿ
  </code></pre>
  
  <p>My goal is to split BINS record into several letter obtaing a recordset like this</p>
  
  <pre><code>MAP_ID  LINE    LEVEL   CHR BINCODE
  -------+-------+-------+---+--------
  9595435 1       2       ÿ   255
  9595435 1       3       ÿ   255
  9595435 1       4       ÿ   255
  9595435 1       5       ÿ   255
  9595435 1       6       ÿ   255
  9595435 1       7       ÿ   255
  9595435 1       8       ÿ   255
  9595435 1       9       ÿ   255
  9595435 1       10      ÿ   255
  9595435 1       11      ÿ   255
  9595435 1       12      þ   254
  9595435 1       13      þ   254
  9595435 1       14      þ   254
  9595435 1       15      þ   254
  9595435 1       16      ÿ   255
  9595435 1       17      ÿ   255
  9595435 1       18      ÿ   255
  9595435 1       19      ÿ   255
  9595435 1       20      ÿ   255
  9595435 1       21      ÿ   255
  9595435 1       22      ÿ   255
  9595435 1       23      ÿ   255
  9595435 1       24      ÿ   255
  9595435 1       25      ÿ   255
  9595435 1       26      ÿ   255
  -------+-------+-------+---+--------
  9595435 2       2       ÿ   255
  9595435 2       3       ÿ   255
  9595435 2       4       ÿ   255
  9595435 2       5       ÿ   255
  9595435 2       6       ÿ   255
  9595435 2       7       ÿ   255
  9595435 2       8       þ   254
  9595435 2       9       þ   254
  9595435 2       10          1
  9595435 2       11          1
  9595435 2       12          10
  9595435 2       13          1
  9595435 2       14          13
  9595435 2       15          17
  9595435 2       16          1
  9595435 2       17      þ   254
  9595435 2       18      þ   254
  9595435 2       19      ÿ   255
  9595435 2       20      ÿ   255
  9595435 2       21      ÿ   255
  9595435 2       22      ÿ   255
  9595435 2       23      ÿ   255
  9595435 2       24      ÿ   255
  9595435 2       25      ÿ   255
  9595435 2       26      ÿ   25
  -------+-------+-------+---+--------
  (...)
  </code></pre>
  
  <p>If I try to solve the issue playing with "CONNECT BY" statement I obtain many duplicate records and I cannot understand why neither what's wrong on my query:</p>
  
  <pre><code>with temp as (
      select
          MAPS.MAP_ID,
          HARD_BIN_LINES.LINE, HARD_BIN_LINES.BINS
      from MAPS, HARD_BIN_LINES
      where MAPS.MAP_ID = 9595435 and MAPS.MAP_ID = HARD_BIN_LINES.MAP_ID
      order by HARD_BIN_LINES.MAP_ID, HARD_BIN_LINES.LINE
  )
  select 
      MAP_ID, LINE, LEVEL,
      substr(BINS,level,1) as CHR, ASCII(substr(BINS,level,1)) as BINCODE
  from temp
  connect by level &lt;= length(BINS)
  </code></pre>