title: Oracle varchar2 field blanks vs null
body: <p><strong>product version</strong>: <strong>Oracle 19C</strong></p>
  <p>I have a partitioned table <code>myTable</code> with a column definition <code>myCol VARCHAR2(2 BYTE)</code></p>
  <p>Recently some data has been loaded using SQL Loader and during the loading process  apparently spaces have been loaded to this column when there is no value.
  I am trying to treat rows with spaces/blanks as null.</p>
  <p><strong>My question</strong>:
  How to find what is contained in the rows that are blank and how to treat those values as null when I select those rows?</p>
  <p>When I issue below query I see no rows returned.</p>
  <pre><code>select * from myTable where trim(myCol) is null
  </code></pre>
  <p>I am not able to figure out what is contained in those rows. Please see below debug queries I tried -</p>
  <pre><code>select '#'||myCol||'#' from myTable ;
  </code></pre>
  <p>output</p>
  <p>--##</p>
  <pre><code>select length(myCol) from myTable ;
  </code></pre>
  <p>output</p>
  <p>--2</p>
  <pre><code>select ascii(substr(myCol, 1,1)) as myCol_ascii_Post1 from myTable ;
  </code></pre>
  <p>output</p>
  <p>--0</p>
  <pre><code>select ascii(substr(myCol, 2,1)) as myCol_ascii_Post2 from myTable ;
  </code></pre>
  <p>--0</p>
  <pre><code>select ascii(substr(myCol, 3,1)) as myCol_ascii_Post3 from myTable ;
  </code></pre>
  <p>output</p>
  <p>--(null)</p>
  <pre><code>select DUMP(myCol) from myTable ;
  </code></pre>
  <p>output</p>
  <p>--Typ=1 Len=2: 0,0</p>
  <pre><code>select myCol,trim(myCol) as myCol_trim,nvl(trim(myCol),'NULL Value') as myCol_trim_nvl from myTable ;
  </code></pre>
  <p>output
  -- blank , blank, blank</p>
  <pre><code>select count(myCol)
  from myTable and trim(myCol) is null;
  </code></pre>
  <p>output</p>
  <p>--0</p>
  <pre><code>select count(myCol) from myTable and trim(myCol) = '';
  </code></pre>
  <p>output</p>
  <p>--0</p>
  <pre><code>select myCol from myTable; 
  </code></pre>
  <p>output</p>
  <p>-- several blank rows</p>
  <pre><code>select count(myCol) from myTable and ascii(myCol) = 0 order by 1 desc;
  </code></pre>
  <p>output</p>
  <p>--345051 rows</p>
  <pre><code>select count(myCol) from myTable and ascii(myCol) &gt; 0 order by 1 desc;
  </code></pre>
  <p>output</p>
  <p>-- 0 rows</p>