====== Oracle - SQL - Find - Find leading and trailing spaces in a varchar2 column ======
===== Find Leading Spaces =====
select mycol from mytab where substr(mycol,1,1) = ' ';
----
===== Find Leading and Trailing Spaces =====
select mycol from mytab where mycol like '% ' or c like ' %';
or
select mycol from mytab where substr(mycol,1,1) = ' ' or substr(mycol,-1,1) = ' ';
or
select mycol from mytab where regexp_like(mycol,'(^ | $)');
----
===== Find Leading and Trailing nonprintable =====
To catch any nonprintable, such as space, carriage return, newline, vertical tab, and form feed:
select mycol from mytab where regexp_like(mycol,'(^[:space:]|[:space:]$)');
**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.