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
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"Received on Thu Jun 20 1996 - 00:00:00 CEST
("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 **** **************************************************************************************