Recursion was required...

#!/usr/bin/perl

use strict; # https://perlmonks.org/?node_id=921111
use warnings;

my $sql = <<END;
select A, B, C,
  (select D, E, F,
    (select G, H from AA where ID = Z) from BB where ID2 = X),
      I, J
        from CC;
END

sub error
  {
  substr $_, pos($_), 0, "\e\[1;31;43m @_ \e[m";
  die $_,"\n";
  }

print "ORIGINAL:\n\n$sql";
my $adding = ' format \\"%s|\\"'; # NOTE after each field name
local $_ = $sql;
selectexpr();
/\G;/gc or error 'incomplete parse';
print "\nMODIFIED:\n\n$_";

sub selectexpr
  {
  /\G\s*select/gc || error 'missing select';
  do {
    /\G\s*(\w+)/gc ? do { # field name found
      my $pos = pos();
      substr $_, $pos, 0, $adding;
      pos() = $pos + length $adding;
      } :
    /\G\s*\(/gc ? (selectexpr(), /\G\s*\)/gc || error 'missing close p
+aren') :
    error 'either name or subselect expected';
    } while /\G\s*,/gc;
  /\G\s*from/gc || error 'missing from';
  /\G\s*\w+/gc || error 'missing table name';
  /\G\s*where[^);]*/gc; # optional
  }
[download]

Outputs:

ORIGINAL:

select A, B, C,
  (select D, E, F,
    (select G, H from AA where ID = Z) from BB where ID2 = X),
      I, J
        from CC;

MODIFIED:

select A format \"%s|\", B format \"%s|\", C format \"%s|\",
  (select D format \"%s|\", E format \"%s|\", F format \"%s|\",
    (select G format \"%s|\", H format \"%s|\" from AA where ID = Z) f
+rom BB where ID2 = X),
      I format \"%s|\", J format \"%s|\"
        from CC;
[download]