User Tools

Site Tools


oracle:sql:find:find_leading_and_trailing_spaces_in_a_varchar2_column

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.
oracle/sql/find/find_leading_and_trailing_spaces_in_a_varchar2_column.txt · Last modified: 2021/08/08 17:24 by peter

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki