Re: Trimming unwanted spaces in ORACLE fields

From: Steve Cosner <stevec_at_zimmer.CSUFresno.EDU>
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))),'" "',

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

Original text of this message