Re: Trimming unwanted spaces in ORACLE fields
Date: 1996/06/20
Message-ID: <4qcg9s$d5d_at_zimmer.CSUFresno.EDU>#1/1
David,
First, your post is entirely appropriate to the comp.databases.oracle newsgroup.
Regarding your select, I have broken apart the "ltrim..." part of your select below with line numbers for the following comments:
1 ltrim(
2 substr(CIVIC_NUMBER, 3 instr(CIVIC_NUMBER,' ',-1,1), 4 length(CIVIC_NUMBER) ) 5 )
- Line 1: Why do you use ltrim for Civic_Number, when you are using Rtrim for all the other columns in your select? I think you need Rtrim to remove _trailing_ spaces.
- Line 3: Using the -1 in instr is going to give you the location of the last space in your data. Either I am reading this wrong, or you aren't really using -1.
Why won't just using RTRIM(CIVIC_NUMBER) work??? If you have trailing and leading spaces, then use: LTRIM(RTRIM(CIVIC_NUMBER)) It looks like your using SUBSTR and INSTR are getting you completely fouled up.
I hope this helps, and that I am not completely wrong with my answer.
Steve Cosner
In article <31C973A3.3E4A_at_city.vancouver.bc.ca>,
David Crossley <david_crossley_at_city.vancouver.bc.ca> wrote:
>Hello to the list,
> 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
> a space is still placed at the end or the beginning of the field.
> The field is defined as VARCHAR2(4).
<snip>
>select '("',rtrim(STD_STREET),'" "',rtrim(LAND_COORD),'" "',
> ltrim(substr(CIVIC_NUMBER,instr(CIVIC_NUMBER,' ',-1,1),
length(CIVIC_NUMBER))),'" "',Received on Thu Jun 20 1996 - 00:00:00 CEST
> rtrim(ADDRESS_STATUS_CODE),'" "',
> rtrim(ADDRESS_TYPE_CODE),'" " ")'
> from address where rownum < 100;
sample output
>("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" " ")