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

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 14 Jul 2006 07:28:13 -0700
Message-ID: <1152887293.224011.140740@p79g2000cwp.googlegroups.com>

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 - 09:28:13 CDT