Trimming unwanted spaces in ORACLE fields

From: David Crossley <david_crossley_at_city.vancouver.bc.ca>
Date: 1996/06/20
Message-ID: <31C973A3.3E4A_at_city.vancouver.bc.ca>#1/1


-- 
Hello to the list,

	I am new to the list, so please let me know if this post is inappropriate.
I have encountered some problems with removing spaces in VARCHAR2 field.  The script and 
the resulting data follow.  Should I be using a different datatype to ensure that the 
spaces can be eliminated ?  Any input would be appreciated.

The problem field is CIVIC_NUMBER.  It is the third field in the sample output.
I have tried using "ltrim" and "rtrim" together but I a space is still placed at the
end or the beginning of the field.  The field is defined as VARCHAR2(4).

SCRIPT USED TO EXTRACT DATA:

spool /gis/dev/sql/cas/addrmstr.txt
set heading off
set pagesize 0
set echo off
set term off
set linesize 80
set feedback off
set space 0
set null ''
select instr (rtrim(civic_number), ' ',-1, 1) from address where rownum < 20;
select '"ADDR_MSTR"' from dual;
select '("STD_STR" "LAND_COORD" "CVC_NUM" "ADDR_STS_CD" "ADDR_TYP" "FILE_LIT_FLG")' from dual;
select	'("',rtrim(STD_STREET),'" "',rtrim(LAND_COORD),'" "',
		ltrim(substr(CIVIC_NUMBER,instr(CIVIC_NUMBER,' ',-1,1),length(CIVIC_NUMBER))),'" "',
		rtrim(ADDRESS_STATUS_CODE),'" "',
		rtrim(ADDRESS_TYPE_CODE),'" " ")' 
		from address where rownum < 100;
spool off
exit

SAMPLE DATASET:

"ADDR_MSTR"                                                                     

("STD_STR" "LAND_COORD" "CVC_NUM" "ADDR_STS_CD" "ADDR_TYP" "FILE_LIT_FLG")
("ABBOTT NORTH FOOT " " " "0 " "1" "MA" " ")
("ABBOTT ST " " " "134 " "1" "MA" " ")
("ABBOTT ST " " " "203 " "1" "SE" " ")
("ABBOTT ST " " " "205 " "1" "SE" " ")
("ABBOTT ST " " " "207 " "1" "SE" " ")
************************************************************************************** **** David Crossley, GIS/AM System and Database Administrator **** **** City of Vancouver, British Columbia, Canada **** **** E-Mail: david_crossley_at_city.vancouver.bc.ca **** **** Phone: (604) 871-6124 **** **************************************************************************************
Received on Thu Jun 20 1996 - 00:00:00 CEST

Original text of this message