Method to check null/empty fields

[shail_arya]

Hey Everyone,

I have a TABLE that has 70 columns. I need to check if any of them is NULL or empty, for a LOGGED on user.
What will be the best way for it.

Thanks
Shail

[WolfShade]

Depends upon what flavour of SQL you’re using.

In Oracle, I use:

SELECT columnName
FROM tableName
WHERE (columnName IS NULL 
                OR columnName = '' 
                OR length(columnName) = 0)

[r937]

@WolfShade

Depends upon what flavour of SQL you’re using.

actually, what you posted will work in all systems

possible exception is the LENGTH() function, as the standard is CHARACTER_LENGTH()

p.s. your last two conditions are equivalent

[WolfShade]

@r937

p.s. your last two conditions are equivalent

I like to be absolutely, positively sure. Just my anal-retentive brain showing one of my pedantic tendencies.

I think some databases will see a blank ‘’ as null (Oracle does, for sure), others will not.

[shail_arya]

@WolfShade

Depends upon what flavour of SQL you’re using.

In Oracle, I use:

SELECT columnName
FROM tableName
WHERE (columnName IS NULL 
                OR columnName = '' 
                OR length(columnName) = 0)

Hi,
My table has 60columns, do i need to write all?

[r937]

@WolfShade

I think some databases will see a blank ‘’ as null (Oracle does, for sure)

that is definitely “messed” up (as well as non-standard)

[r937]

@shail_arya

My table has 60columns, do i need to write all?

of course

[shail_arya]

@r937

of course

then m dead… no array option here?

[SpacePhoenix]

Might be easier to have whatever server side language you’re using fire off a SELECT query to get the table then loop through each row of the result set and have oit loop through each field of result set to look for null or empty fields and do whatever you need to do with the fields. Then once a row of the result set has been dealt with, if any changes have been made, fire off an UPDATE query to write the changes to the database.

[shail_arya]

@SpacePhoenix

Might be easier to have whatever server side language you’re using fire off a SELECT query to get the table then loop through each row of the result set and have oit loop through each field of result set to look for null or empty fields and do whatever you need to do with the fields. Then once a row of the result set has been dealt with, if any changes have been made, fire off an UPDATE query to write the changes to the database.

Hey hi… I m using PHp…
when a user will login in, it will check his profile data and if any field is empty , will give a update message to him. But i don’t know how to use array in that.

[WolfShade]

The requirement is that ALL 60 COLUMNS must be filled with data? 60???

(The client must be NSA.)

[r937]

60 columns suggests a possible opportunity for normalization