Re: newbie:sql trim() in where clause not working

joes wrote:
> Many thanks for your help.
> I did a "dump" and have seen that all entries have been spaces. I
> BASE10 BASE16
> Typ=1 Len=5: 32,32,32,0,0 Typ=1 Len=5: 20,20,20,0,0
>
> Unfortunately your second suggestion did not work. The statement
>
> > UT1 > select * from marktest where ltrim(fld1) is null;
>
> did not return any reult.
> hope that the other 2 characters '0' are ignored or are these null
> characters?
>
> regards
> Mark
>
>
> Mark D Powell schrieb:
>
> > joes wrote:
> > > Hi
> > >
> > > I have somestring entries which consists only of spaces. I like to get
> > > rid of those entries but I can not "select" them. Both examples will
> > > not return anything
> > >
> > > select * from tableA where trim(field1) is NULL
> > > select * from tableAt where trim(field1) = ''
> > >
> > > Best options would be to do this during the importing job,
> > > unfortunately I can not repat this import. So any other suggestions?
> > >
> > > Many thanks in advance
> > >
> > > regards
> > > Mark
> >
> > Mark, look up the dump function in the SQL manual and exaimine some of
> > the columns in question to be sure the contents is one of more spaces
> > and not nulls or carriage returns, etc....
> >
> > UT1 > l
> > 1 select fld1, dump(fld1,10) Base10, dump(fld1,16) Base16
> > 2 from marktest
> > 3* where rownum = 1
> > UT1 > /
> >
> > FLD1
> > ----------
> > BASE10
> > --------------------------------------------------------------------------------
> > BASE16
> > --------------------------------------------------------------------------------
> > one
> > Typ=1 Len=3: 111,110,101
> > Typ=1 Len=3: 6f,6e,65
> >
> >
> > You should be able to use the LTRIM function to find the target rows:
> >
> > UT1 > select * from marktest where ltrim(fld1) is null;
> >
> > FLD1 FLD2 FLD3
> > ---------- ---------- ---------
> > 99 01-JAN-50
> >
> > UT1 > select dump(fld1,10) from marktest where fld2 = 99;
> >
> > DUMP(FLD1,10)
> > --------------------------------------------------------------------------------
> > Typ=1 Len=1: 32
> >
> > HTH -- Mark D Powell --

Zero is the null character. You have managed to insert trailing nulls into your character columns. You can use the RTRIM function to remove all characters that appear in a set. If you trimmed both the null character and the space character you should end up with a null column.

HTH -- Mark D Powell -- Received on Sat Jul 15 2006 - 17:07:16 CDT