OraFAQ Forum: SQL & PL/SQL » Length is null

Home » SQL & PL/SQL » SQL & PL/SQL » Length is null

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Length is null [message #20638]

Mon, 10 June 2002 08:30

Kristi
Messages: 4
Registered: June 2002

Junior Member

Hi,

What would make the length of a field null? I have 300,000+ social security numbers and only 41,000 show up with a length value.

I am unable to make the updates to this field since length is null and substr is not working on a null field length.

thanks, in advance.

Report message to a moderator

Re: Length is null [message #20639 is a reply to message #20638]

Mon, 10 June 2002 10:02

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

still you can use IS NULL to update a null field.

SQL> update emp set comm=10 where length(comm) is null;

10 rows updated.

Report message to a moderator

Re: Length is null [message #20641 is a reply to message #20638]

Mon, 10 June 2002 11:33

sridhar
Messages: 119
Registered: December 2001

Senior Member

UPDATE EMP SET comm = 10 WHERE COMM is NULL;

Thx,
SriDHAR

Report message to a moderator

Re: Length is null [message #20648 is a reply to message #20638]

Mon, 10 June 2002 22:20

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You could use a DECODE or NVL.

SELECT substr(nvl(comm,0),1,1)
FROM emp

select substr(decode(comm,NULL,0,comm),1,1)
from emp

For updates, the mechanism is alike.

MHE

Report message to a moderator

Re: Length is null [message #20672 is a reply to message #20638]

Wed, 12 June 2002 18:42

Su
Messages: 154
Registered: April 2002

Senior Member

Add a little condition or DECODE() to check the field is whether NULL, if it is NULL, then dont check the length of it. Since I am not sure what the query is, try to include this WHERE FIELD1 IS NOT NULL AND LENGTH(FIELD1)=whatever
You can also make use of DECODE(). Check it out.
Good luck :)

Report message to a moderator

Previous Topic:	Cannot drop table-Urgent
Next Topic:	1-field table?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 04:14:17 CDT 2024