how to find if a column has white space in a table - oracle

Question

I am trying to find if a certain column requires TRIM function on it.

How can I find out if this column in a table has records that have white space either before or after the actual data.

Answer 1

A quick and dirty way

WHERE LENGTH(TRIM(COL1)) <> LENGTH(COL1)

Answer 2

You could use regular expressions in Oracle.

Example:

select * from your_table 
 where regexp_like(your_column, '^[ ]+.*')
    or regexp_like(your_column, '.*[ ]+$')

Answer 3

You can check it using the TRIM function itself, not the most efficient but accurate:

Select *
From TableA
Where MyColumn <> TRIM(MyColumn)

Though if you're checking then turning around to trim anyway, you probably want to just do it in the first place, like this:

Select TRIM(MyColumn) as TrimmedMyColumn
From TableA