How do I write a query to check whether a column
(varchar2) has leading spaces or trailing spaces around the text value?
Finding trailing spaces is more challenging. Oracle ACE Laurent
Schneider provided this elegant solution to finding leading spaces and trailing
spaces in a varchar2 column:
select mycol from mytab
where substr(mycol,1,1) = ' ' or substr(mycol,-1,1) = ' ';
--
Carmelinda Richard
It should also be added that, with Oracle 11, if
there are concerns with more than one possible whitespace characters 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.
select mycol from
mytab where
regexp_like(mycol,'(^[:space:]|[:space:]$)');
Will catch any space characters (nonprinting), such
as space, carriage return, newline, vertical tab, and form feed If all you
want to check for is the space character, you can also do that with
regexp_like:
select mycol from
mytab where regexp_like(mycol,'(^ | $)');
However I have generally found this to run slower
than the already offered options in this tip for such a simple test. The
power of REGEXP is better suited for more complex checks.
Mike Broughton
Oracle Consultant
