SQL Oracle——查找带有前导或尾随空格的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3223226/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle -- finding values with leading or trailing spaces
提问by learn_plsql
I am trying to find if a certain column requires TRIMfunction on it.
我试图找出某个列是否需要TRIM函数。
How can I find out if this column in a table has records that have white space either before or after the actual data.
如何确定表中的此列是否包含在实际数据之前或之后具有空格的记录。
回答by Nick Craver
You can check it using the TRIMfunction itself, not the most efficient but accurate:
您可以使用TRIM函数本身来检查它，不是最有效但最准确的：
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
回答by Martin Smith
A quick and dirty way
一种快速而肮脏的方式
WHERE LENGTH(TRIM(COL1)) <> LENGTH(COL1)
回答by learn_plsql
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.
这似乎工作得很好。
回答by Pablo Santa Cruz
You could use regular expressionsin Oracle.
您可以在 Oracle 中使用正则表达式。
Example:
例子：
select * from your_table
where regexp_like(your_column, '^[ ]+.*')
or regexp_like(your_column, '.*[ ]+$')
回答by vinesh
select data1, length(data1)-length(replace(data1,' ','')) from t;
回答by Jay Sitamraju
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;
当表字段 T$DSCA 在末尾有尾随空格时，以下查询将检索行： SELECT * from TABLE_NAME A WHERE RAWTOHEX(SUBSTR(AT$DSCA, LENGTH(T$DSCA),1)) ='A0' AND TRIM (T$DSCA) 不为空；
