Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: newbie:sql trim() in where clause not working
alternatively, try
select * from table a where length(trim(field1)) = 0
Charles Hooper wrote:
> 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
>
> SELECT
> *
> FROM
> TABLEA
> WHERE
> FIELD1 LIKE ' %';
> (Note that there is a space before %)
>
> SELECT
> *
> FROM
> TABLEA
> WHERE
> SUBSTR(FIELD1,1,1)=' ';
> (Note that there is a space between '')
>
> SELECT
> *
> FROM
> TABLEA
> WHERE
> NVL(LENGTH(TRIM(FIELD1)),0)=0;
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Fri Jul 14 2006 - 15:00:03 CDT