Oracle - how to remove spaces?

Question

I run this statement:

select trim(a),trim(b) from table x; 

Although I used the trim () statement, my output is as follows:

 AB ___ ____ kunjramansingh smartdude 

The column data types "a" and "b" are varchar2 (255).

There is a gap between the data of the two outputs. I want to show data without spaces - in this format:

 AB ___ ______ kunjramansinghsmartdude 

How can i do this?

Answer 1

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

Update:

Just string concatenation:

 SELECT a || b FROM mytable 

Answer 2

It looks like you are executing a query in sqlplus. Sqlplus must make sure that there is enough space between the columns, so you can display the maximum row size (255). Typically, the solution should use the column formatting options (run before the query: A20 column format) to reduce the maximum row size (rows that exceed this length will be displayed on multiple rows).

Answer 3

If I understand correctly, this is what you want

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

Answer 4

Does this sound like an output formatting problem? If you are using SQL Plus, use the COLUMN command similar to this one (if you want the maximum screen width to be 20 characters each):

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

Answer 5

you can use "rpad" in your select request and specify the size ...

 select rpad(a , 20) , rpad(b, 20) from x ; 

where the first parameter is your column name and the second parameter is the size you want to use.

Answer 6

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

To confirm that your output does not actually contain these extra spaces, try the following:

 SELECT '/' || TRIM(A) || '/' AS COLUMN_A ,'/' || TRIM(B) || '/' AS COLUMN_B FROM MY_TABLE; 

If the "/" characters are separated from your output, this means that these are not spaces, but some other space characters that fall there (for example, tabs). If so, then this is probably the input validation problem in your application.

However, the most likely scenario is that the "/" characters will actually touch the rest of your lines, thereby proving that spaces are actually trimmed.

If you want to bring them together, then Quassnoi's answer should do it.

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

Answer 7

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

Answer 8

Say we have a column with values ​​consisting of alphanumeric characters and only underscores. We need to crop this column from all spaces, tabs or any white characters. The following is an example of a problem. For comparison, cropped and original are displayed. select '/'||REGEXP_REPLACE(my_column,'[^AZ,^0-9,^_]','')||'/' my_column,'/'||my_column||'/' from my_table;

Answer 9

Use the following to make sure there are no spaces in your output:

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

Exit

John Smith

Jane doe

Answer 10

If you want to replace spaces in a specific 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); 

Answer 11

Run the query below, replacing TABLE_NAME and COLUMN_NAME with the actual table and column names:

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