Oracle - 如何删除空格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1195566/
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 - how to remove white spaces?
提问by Plasmer
I am running this statement:
我正在运行这个语句：
select trim(a),trim(b) from table x;
Even though I used the trim() statement, my output looks like this:
尽管我使用了 trim() 语句，但我的输出如下所示：
A B
___ ____
kunjramansingh smartdude
The datatype of column 'a' and 'b' is varchar2(255).
'a' 和 'b' 列的数据类型是 varchar2(255)。
There is a gap between the data of two output. I want to show the data without the whitespace - in a format like this:
两个输出的数据之间存在差距。我想显示没有空格的数据 - 格式如下：
A B
___ ______
kunjramansinghsmartdude
How can I do this?
我怎样才能做到这一点？
回答by Quassnoi
SELECT REGEXP_REPLACE('A B_ __ kunjramansingh smartdude', '\s*', '')
FROM dual
---
AB___kunjramansinghsmartdude
Update:
更新：
Just concatenate strings:
只需连接字符串：
SELECT a || b
FROM mytable
回答by Plasmer
It looks like you are executing the query in sqlplus. Sqlplus needs to ensure that there is enough room in the column spacing so the maximum string size can be displayed(255). Usually, the solution is to use the column formatting options(Run prior to the query: column A format A20) to reduce the maximum string size (lines that exceed this length will be displayed over multiple lines).
看起来您正在 sqlplus 中执行查询。Sqlplus 需要保证列间距有足够的空间才能显示最大字符串大小（255）。通常，解决方案是使用列格式选项（在查询之前运行：列 A 格式 A20）来减小最大字符串大小（超过此长度的行将显示在多行中）。
回答by curtisk
If I understand correctly, this is what you want
如果我理解正确，这就是你想要的
select (trim(a) || trim(b))as combinedStrings from yourTable
回答by Gaurav Singh
you can use 'rpad' in your select query and specify the size ...
您可以在选择查询中使用“rpad”并指定大小...
select rpad(a , 20) , rpad(b, 20) from x ;
where first parameter is your column name and second parameter is the size which you want to pad with .
其中第一个参数是您的列名，第二个参数是您要填充的大小。
回答by Tony Andrews
This sounds like an output formatting issue? If using SQL Plus, use the COLUMN command like this (assuming you want a maximum display width of 20 chars for each):
这听起来像是输出格式问题？如果使用 SQL Plus，请像这样使用 COLUMN 命令（假设您希望每个最大显示宽度为 20 个字符）：
column a format a20
column b format a20
select a, b from mytable;
回答by JosephStyons
SQL Plus will format the columns to hold the maximum possiblevalue, which in this case is 255 characters.
SQL Plus 将格式化列以保存可能的最大值，在本例中为 255 个字符。
To confirm that your output does not actually contain those extra spaces, try this:
要确认您的输出实际上不包含这些额外的空格，请尝试以下操作：
SELECT
'/' || TRIM(A) || '/' AS COLUMN_A
,'/' || TRIM(B) || '/' AS COLUMN_B
FROM
MY_TABLE;
If the '/' characters are separated from your output, then that indicates that it's not spaces, but some other whitespace character that got in there (tabs, for example). If that is the case, then it is probably an input validation issue somewhere in your application.
如果“/”字符与您的输出分开，则表明它不是空格，而是进入其中的其他一些空白字符（例如制表符）。如果是这种情况，那么它可能是您的应用程序中某处的输入验证问题。
However, the most likely scenario is that the '/' characters will in fact touch the rest of your strings, thus proving that the whitespace is actually trimmed.
但是，最可能的情况是“/”字符实际上会接触到您的字符串的其余部分，从而证明空格实际上已被修剪。
If you wish to output them together, then the answer given by Quassnoi should do it.
如果你想把它们一起输出，那么 Quassnoi 给出的答案应该这样做。
If it is purely a display issue, then the answer given by Tony Andrews should work fine.
如果这纯粹是一个显示问题，那么 Tony Andrews 给出的答案应该可以正常工作。
回答by JosephStyons
REPLACE(REPLACE(a.CUST_ADDRESS1,CHR(10),' '),CHR(13),' ') as ADDRESS
回答by BIJUNATOR
Say, we have a column with values consisting of alphanumeric characters and underscore only. We need to trim this column off all spaces, tabs or whatever white characters.
The below example will solve the problem. The trimmed one and the original one both are being displayed for comparison.
select '/'||REGEXP_REPLACE(my_column,'[^A-Z,^0-9,^_]','')||'/' my_column,'/'||my_column||'/' from my_table;
比如说，我们有一列的值仅由字母数字字符和下划线组成。我们需要修剪掉所有空格、制表符或任何白色字符的这一列。下面的例子将解决这个问题。修剪后的和原始的都显示出来进行比较。
select '/'||REGEXP_REPLACE(my_column,'[^A-Z,^0-9,^_]','')||'/' my_column,'/'||my_column||'/' from my_table;
回答by Chikku Jacob
If you would like to replace white spaces in a particular column value, you can use the following script to do the job for you,
如果您想替换特定列值中的空格，您可以使用以下脚本为您完成这项工作，
UPDATE TableName TN
SET TN.Column_Name = TRIM (TN.Column_Name);
回答by Ved Prakash
I have used below command to remove white space in Oracle
我使用以下命令删除 Oracle 中的空格
My TableName is - NG_CAP_SENDER_INFO_MTR
我的表名是 - NG_CAP_SENDER_INFO_MTR
My Column Nameis - SENINFO_FROM
我的列名是 - SENINFO_FROM
UPDATE NG_CAP_SENDER_INFO_MTR SET SENINFO_FROM = TRIM(SENINFO_FROM);
Already Answer on StackOverflow LTRIM RTRIM
已经在 StackOverflow LTRIM RTRIM 上回答
And its working fine
它的工作正常
