Oracle - how to remove white spaces?

Question

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?

Answer 1

SELECT  REGEXP_REPLACE('A B_ __ kunjramansingh smartdude', '\s*', '')
FROM    dual

---
AB___kunjramansinghsmartdude

Update:

Just concatenate strings:

SELECT  a || b
FROM    mytable

Answer 2

If I understand correctly, this is what you want

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

Answer 3

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 a20
column b a20
select a, b from mytable;

Answer 4

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.

Answer 5

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