CODEWITHSUNDEEP
sqloracle-database
learn_plsql
learn_plsql

Reputation: 1681

Oracle -- finding values with leading or trailing spaces

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.

Upvotes: 8

Views: 49873

Answers (6)

user8216402
user8216402

Reputation:

So why can't you use the following to find the leading spaces? I've been able to identify the records with leading spaces this way and using '% ' to find the trailing spaces. 

SELECT mycolumn
FROM my_table
WHERE mycolumn LIKE ' %'

I've also used the following to remove both the leading and trailing spaces

Update My_table set Mycolumn = TRIM(Mycolumn)

which seems to work just fine.

Upvotes: 2

Jay Sitamraju
Jay Sitamraju

Reputation: 1

Following query will retrieve rows when one of Table fields T$DSCA has trailing spaces at the end: SELECT * from TABLE_NAME A WHERE RAWTOHEX(SUBSTR(A.T$DSCA, LENGTH(T$DSCA),1)) ='A0' AND TRIM(T$DSCA) is not null;

Upvotes: -1

vinesh
vinesh

Reputation: 1

select data1, length(data1)-length(replace(data1,' ','')) from t;

Upvotes: -1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181290

You could use regular expressions in Oracle.

Example: 

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453328

A quick and dirty way

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

Upvotes: 10

Nick Craver
Nick Craver

Reputation: 630439

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

Upvotes: 16

Related Questions