Home » SQL & PL/SQL » SQL & PL/SQL » Truncate trailing spaces on select
Truncate trailing spaces on select [message #192284] Mon, 11 September 2006 12:29 Go to next message
llreynolds
Messages: 1
Registered: September 2006
Junior Member
I'm trying to truncate trailing spaces from a field. The fields I'm working with are 200 chars. I want all chars but the blanks.

I've tried: select rpad(msds.hmis_msds_ser_nr,6,','), rpad(CAS_ID,13,','),
rpad(specific_chem_nm,201,','), rpad(common_nm,201,',')

which gives me all contents.

I've tried:select rpad(msds.hmis_msds_ser_nr,' '), length(rpad(msds.hmis_msds_ser_nr,6,'|')
), rpad(CAS_ID,13),'|',
rtrim(specific_chem_nm,'|'), rtrim(common_nm,'|')

Nothing is working. Any ideas?
Re: Truncate trailing spaces on select [message #192287 is a reply to message #192284] Mon, 11 September 2006 12:37 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Simply rtrim(column_name) should trim the trailing spaces.
Re: Truncate trailing spaces on select [message #192289 is a reply to message #192284] Mon, 11 September 2006 12:37 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
Seems like the rtrim function should do what you need.



SQL> create table test_data (col1   varchar2(200));

Table created.

SQL> insert into test_data values ( rpad('this is a test', 200) );

1 row created.


SQL> select length( col1 ) from test_data;

LENGTH(COL1)
------------
         200

SQL> select rtrim( col1 ) from test_data;

RTRIM(COL1)
-----------------------------------------------------------------
this is a test


SQL> select length( rtrim(col1) ) 
     from test_data;

LENGTH(RTRIM(COL1))
-------------------
                 14
Re: Truncate trailing spaces on select [message #192427 is a reply to message #192284] Tue, 12 September 2006 06:13 Go to previous messageGo to next message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
Hi,

This may solve your problem
"select replace(column-name ,'') from table name;"
Re: Truncate trailing spaces on select [message #192456 is a reply to message #192427] Tue, 12 September 2006 08:19 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
jaydeep mitra wrote on Tue, 12 September 2006 07:13


"select replace(column-name ,'') from table name;"


This is an incorrect answer.
testdb FOO> l
  1* select length(replace ('xxx                     ','')) from dual
testdb FOO> /

LENGTH(REPLACE('XXX',''))
-------------------------
                       24
Previous Topic: Export to CSV issue
Next Topic: Insert into long raw field
Goto Forum:
  


Current Time: Thu Dec 08 05:57:16 CST 2016

Total time taken to generate the page: 0.12898 seconds