Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Oracle - SQL - Find - Find leading and trailing spaces in a varchar2 column ======

===== Find Leading Spaces =====

<code sql>
select mycol from mytab where substr(mycol,1,1) = ' ';
</code>

----

===== Find Leading and Trailing Spaces =====

<code sql>
select mycol from mytab where mycol like '% ' or c like ' %';
</code>

or

<code sql>
select mycol from mytab where substr(mycol,1,1) = ' ' or substr(mycol,-1,1) = ' ';
</code>

or

<code sql>
select mycol from mytab where regexp_like(mycol,'(^ | $)');
</code>

----

===== Find Leading and Trailing nonprintable =====

To catch any nonprintable, such as space, carriage return, newline, vertical tab, and form feed:

<code sql>
select mycol from mytab where regexp_like(mycol,'(^[:space:]|[:space:]$)');
</code>

<WRAP info>
**NOTE:**  With Oracle 11, if there are concerns with more than one possible white-space character before or after a text value then the new **REGEXP_LIKE** functionality is far superior to checking for each possible character value with the techniques offered (e.g.) Also checking for leading/trailing tabs, newlines, etc.

  * The power of REGEXP is better suited for more complex checks, as it is usually slower.
</WRAP>