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

joes wrote:
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 --

joes wrote:
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.

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

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:

Sorry for posting again but I did a small extension

select standort, dump(trim(fld1),10) Base10, dump(trim(fld1),16) Base16
from transaktion
where rownum = 1

in order to see if the null characters are an issue. The result is

BASE10 BASE16
Typ=1 Len=2: 0,0 Typ=1 Len=2: 0,0

So exist there a possibility to TRIM the null characters?

regards
Mark


joes schrieb:

alternatively, try

select * from table a where length(trim(field1)) = 0

Charles Hooper wrote:

On 14 Jul 2006 13:00:03 -0700, "akkha1234 (AT) gmail (DOT) com"
<akkha1234 (AT) gmail (DOT) com> wrote:

Please do not top post

--
Sybrand Bakker, Senior Oracle DBA

joes wrote:
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 --