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.
-
Do you mean "whitespace" or "space"? The former includes tabs, and the accepted answer won't handle that.David Aldridge– David Aldridge2013-07-15 08:22:47 +00:00Commented Jul 15, 2013 at 8:22
Add a comment
|
6 Answers
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
3 Comments
bwawok
yes this works. but realize this means every query is a full table scan. If doing a lot of queries, consider updating your data to not have spaces.
Nick Craver
@bwawok - +1 - If you're always using it like this it's tremendously better to make it a once time cost and do an update, wasn't sure of the OPs situation in this case.
David Aldridge
@bwawok -- or a fast full index scan.
A quick and dirty way
WHERE LENGTH(TRIM(COL1)) <> LENGTH(COL1)
Comments
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.
Comments
You could use regular expressions in Oracle.
Example:
select * from your_table
where regexp_like(your_column, '^[ ]+.*')
or regexp_like(your_column, '.*[ ]+$')
answered Jul 11, 2010 at 13:39
Pablo Santa Cruz
182k33 gold badges250 silver badges300 bronze badges
1 Comment
Martin Smith
Your example doesn't need Regex - It would just be
your_column LIKE ' %' OR your_column LIKE '% ' I think the Regex woul;d need to be ^[\s]+.* to match all white spaceselect data1, length(data1)-length(replace(data1,' ','')) from t;
Comments
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;
2 Comments
gitsitgo
Consider describing your solution instead of "here's the code, goodbye."
Toby Speight
Welcome to Stack Overflow! Although this code may help to solve the problem, it doesn't explain why and/or how it answers the question. Providing this additional context would significantly improve its long-term educational value. Please edit your answer to add explanation, including what limitations and assumptions apply.