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 --

.

Relevant Pages

  • Re: cannot read Bluescreen info
    ... i had a look and i dont see any entries which give me a hint. ... Watson give some hint?. ... But i do not get any memory dump to analyze my ... >> What is wrong or what can i do to analyze the cause of the bluescreen. ...
    (microsoft.public.windowsxp.help_and_support)
  • Re: Is the a way to print only he auto correct entries that I created.
    ... If someone took the time to install Office on a clean system -- maybe a virtual ... machine -- and use the AutoCorrect template to dump the default set of entries, ... entries from the complete dump. ...
    (microsoft.public.word.docmanagement)
  • System.Diagnostics.EventLog Safe Clearing of the logs
    ... Can anyone give me a couple pointers on safely clearing the logs? ... extremely active environment and I am worried that even after I dump all the ... entries a dozen or so new ones will be created before I can call the Clear ... that get created between the time my dump process finishes and the time I ...
    (microsoft.public.dotnet.general)
  • Re: Dumping COMPLETE slocate (updatedb) database?
    ... > to dump the contents of the db just run ... > The forward slash should match all entries in your locate db. ... Alexander Skwar ...
    (comp.os.linux.misc)
  • RE: if else question
    ... >> The first time I wrote this if else statement I wrote it ... Mark, thank you for you help but actually the statement below is the one I'm ... having trouble getting correct. ... error_log will have 50 entries for line item one and 50 entries for line ...
    (perl.beginners)