CODEWITHSUNDEEP
oracle-databasesqlplusoracle12c
ohh
ohh

Reputation: 193

Removing spaces in column data in Oracle 12.1 - SQLPlus

We are using Oracle based ERP and one of our column, has a specific rule which says that we have a several spaces in the begining and the end of each string. It looks something like this:

       A          |          B
--------------------------------------
   Value1         |123
   Value2         |345

There are 3 spaced in front and 9 spaces after each value in A column. I'm trying to extract the information using SQLPlus script:

SET colsep ,
SET pagesize 0
SET feedback OFF
SET heading OFF
SET trimspool ON
SET trimout ON
spool d:\spool.csv
select trim(rtrim(A)), B, from table;
spool out

The result is as follows:

Value1         ,123
Value2         ,345

What I need is:

Value1,123
Value2,345

Any idea how to achieve that? Tried trim, rtrim, replace, regexp_replace but it does not seem to work.

Upvotes: 1

Views: 5330

Answers (1)

Utsav
Utsav

Reputation: 8143

Use 

select replace(col1||','||col2,' ','') from your_table;

Example

SQL> create table test12(col1 char(10),col2 integer);

Table created.

SQL> insert into test12(col1,col2) values ('value1',1);

1 row created.

SQL> insert into test12(col1,col2) values ('value2',2);

1 row created.

SQL> commit;

Commit complete.

SQL> select replace(col1||','||col2,' ','') from test12;
value1,1
value2,2
SQL> spool off;
SQL>

test.csv

SQL> select replace(col1||','||col2,' ','') from test12;
value1,1
value2,2
SQL> spool off;

Upvotes: 2

Related Questions