SQL Snippets ™: Tutorials for Oracle Developers

This tutorial shows how to extract values from fixed format strings like this

 NUM STRING               NOTE
---- -------------------- -------------------------
   1 a                    One value
   2 a                    Two values, one empty
   3 a                    Three values, two empty
   4      b,b             Three values, two empty
   5           c c c      Three values, two empty
   6 a    b,b             Two values
   7 a    b,b             Three values, one empty
   8 a    b,b  c c c      Three values, none empty
   9                      One empty value
  10                      Two values, two empty
  11                      Three values, all empty
  12                      NULL string
 

and return the values in separate columns like this.

 NUM VAL_1  VAL_2  VAL_3
---- ------ ------ ------
   1 a
   2 a
   3 a
   4        b,b
   5               c c c
   6 a      b,b
   7 a      b,b
   8 a      b,b    c c c
   9
  10
  11
  12
 

This solution uses the SUBSTR function to locate and extract individual values.

set colsep '    '

column string   format a15
column string_2 format a17

select
  num ,
  string ,
  '''' || string || '''' as string_2 ,
  substr( string, 1, 5  ) val_1 ,
  substr( string, 6, 5  ) val_2 ,
  substr( string, 11, 5 ) val_3
from  t1
where data_set = 'Fixed Format'
order by num ;
 

 NUM    STRING             STRING_2             VAL_1     VAL_2     VAL_3
----    ---------------    -----------------    ------    ------    ------
   1    a                  'a    '              a
   2    a                  'a       '           a
   3    a                  'a              '    a
   4         b,b           '     b,b  '                   b,b
   5              c c c    '          c c c'                        c c c
   6    a    b,b           'a    b,b  '         a         b,b
   7    a    b,b           'a    b,b  '         a         b,b
   8    a    b,b  c c c    'a    b,b  c c c'    a         b,b       c c c
   9                       '     '
  10                       '          '
  11                       '               '
  12                       ''
 

Note: set colsep ' ' is used in the examples on this page to improve readability. It is not a component of any solution. Also, columns with single quotes around each value, like STRING_2, will be added to each query to highlight the beginning and end of values that contain trailing spaces. Such columns are not a required part of any solution either.

Be aware that some of the extracted values have trailing spaces and some do not, depending on whether spaces existed in certain positions in the STRING value or not. The following query shows where the trailing spaces are.

column val_1_2 format a7
column val_2_2 format a7
column val_3_2 format a7

select
  num ,
  '''' || substr( string, 1, 5  ) || '''' val_1_2 ,
  '''' || substr( string, 6, 5  ) || '''' val_2_2 ,
  '''' || substr( string, 11, 5 ) || '''' val_3_2
from  t1
where data_set = 'Fixed Format'
order by num ;
 

 NUM    VAL_1_2    VAL_2_2    VAL_3_2
----    -------    -------    -------
   1    'a    '    ''         ''
   2    'a    '    '   '      ''
   3    'a    '    '     '    '     '
   4    '     '    'b,b  '    ''
   5    '     '    '     '    'c c c'
   6    'a    '    'b,b  '    ''
   7    'a    '    'b,b  '    ''
   8    'a    '    'b,b  '    'c c c'
   9    '     '    ''         ''
  10    '     '    '     '    ''
  11    '     '    '     '    '     '
  12    ''         ''         ''
 

To remove trailing spaces from the extracted values use the RTRIM function.

select
  num ,
  '''' || rtrim( substr( string, 1, 5  ) ) || '''' val_1_2 ,
  '''' || rtrim( substr( string, 6, 5  ) ) || '''' val_2_2 ,
  '''' || rtrim( substr( string, 11, 5 ) ) || '''' val_3_2
from  t1
where data_set = 'Fixed Format'
order by num ;
 

 NUM    VAL_1_2    VAL_2_2    VAL_3_2
----    -------    -------    -------
   1    'a'        ''         ''
   2    'a'        ''         ''
   3    'a'        ''         ''
   4    ''         'b,b'      ''
   5    ''         ''         'c c c'
   6    'a'        'b,b'      ''
   7    'a'        'b,b'      ''
   8    'a'        'b,b'      'c c c'
   9    ''         ''         ''
  10    ''         ''         ''
  11    ''         ''         ''
  12    ''         ''         ''