[oracle] Oracle - how to remove white spaces?

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:

A                                                            B                              
___                                                          ____
kunjramansingh                                               smartdude

The datatype of column 'a' and 'b' is 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?

Run below query replacing TABLE_NAME & COLUMN_NAME with your actual table & column names:

UPDATE TABLE_NAME SET COLUMN_NAME=TRIM(' ' from COLUMN_NAME);

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).

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):

column a format a20
column b format a20
select a, b from mytable;

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);

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('A B_ __ kunjramansingh smartdude', '\s*', '')
FROM    dual

---
AB___kunjramansinghsmartdude

Update:

Just concatenate strings:

SELECT  a || b
FROM    mytable

Use the following to insure there is no whitespace in your output:

select first_name || ',' || last_name from table x;

Output

John,Smith

Jane,Doe

If I understand correctly, this is what you want

select (trim(a) || trim(b))as combinedStrings from yourTable

you can use 'rpad' in your select query and specify the size ...

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 .

SQL Plus will format the columns to hold the maximum possible value, which in this case is 255 characters.

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.

If it is purely a display issue, then the answer given by Tony Andrews should work fine.

REPLACE(REPLACE(a.CUST_ADDRESS1,CHR(10),' '),CHR(13),' ') as ADDRESS

I have used below command to remove white space in Oracle

My Table Name is - NG_CAP_SENDER_INFO_MTR

My Column Name is - SENINFO_FROM

UPDATE NG_CAP_SENDER_INFO_MTR SET SENINFO_FROM = TRIM(SENINFO_FROM); 

Already Answer on StackOverflow LTRIM RTRIM

And its working fine