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

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 --
Received on Fri Jul 14 2006 - 10:02:52 CDT