SQL: finding out if a column is NULL /without/ downloading all the content
-
Hi all, Is there any way to write an SQL SELECT statement that will tell me whether a column is NULL or not, without having to download the column's content? I'm working on a content-management system that stores HTML pages as text records in a MS SQL Server table. Some pages have a 'default' page which is displayed by setting the text column to NULL. For the web interface I'd like a way to summarize the state of the content (e.g. whether it has been supplied or not), but it seems ridiculous to download the entire HTML content of a section just to see if it's NULL or not. Oh, and I can't use Stored Procedures for reasons I won't go into :(. TIA, Pete
-
Hi all, Is there any way to write an SQL SELECT statement that will tell me whether a column is NULL or not, without having to download the column's content? I'm working on a content-management system that stores HTML pages as text records in a MS SQL Server table. Some pages have a 'default' page which is displayed by setting the text column to NULL. For the web interface I'd like a way to summarize the state of the content (e.g. whether it has been supplied or not), but it seems ridiculous to download the entire HTML content of a section just to see if it's NULL or not. Oh, and I can't use Stored Procedures for reasons I won't go into :(. TIA, Pete
Well, you could always do something like this: SELECT id FROM tblContent WHERE ... AND Content IS NULL Modify it as you want to, the most important thing is the condition "column IS NULL" can be used to find out whether a column is null or not without having to download it. Hope it helps, Edbert
-
Well, you could always do something like this: SELECT id FROM tblContent WHERE ... AND Content IS NULL Modify it as you want to, the most important thing is the condition "column IS NULL" can be used to find out whether a column is null or not without having to download it. Hope it helps, Edbert
Hi Edbert, thanks for the reply. I'd thought of that, but I'm not sure how well it works if I'm displaying info about /every/ row in the table. I'd have to do a SELECT for each row. Plus, each row has about 5 different text fields (each 'page' of content has 5 'sub-pages'). So that would mean 5*num_rows SELECTs! I guess a similar approach would be to initially do the SELECT you suggest (for each of the 5 content columns), and then programmatically compare each id to the results. Hmmm, maybe that's what you meant in the first place. It's annoying that there doesn't seem to be a 'cleaner' way to do it, but then I guess SQL isn't really the most powerful thing for this kind of problem... Thanks for you help, Pete
-
Hi Edbert, thanks for the reply. I'd thought of that, but I'm not sure how well it works if I'm displaying info about /every/ row in the table. I'd have to do a SELECT for each row. Plus, each row has about 5 different text fields (each 'page' of content has 5 'sub-pages'). So that would mean 5*num_rows SELECTs! I guess a similar approach would be to initially do the SELECT you suggest (for each of the 5 content columns), and then programmatically compare each id to the results. Hmmm, maybe that's what you meant in the first place. It's annoying that there doesn't seem to be a 'cleaner' way to do it, but then I guess SQL isn't really the most powerful thing for this kind of problem... Thanks for you help, Pete
-
If you post the database structure and how you want your query to be I might be able to help you build the query.
You could use ISNULL( column, valueIfNull ) although you will still end up with the data if the column is not null. Your best bet would be to use a case e.g. CASE WHEN col IS NULL THEN 1 ELSE 0 END AS IsColNull. Please note this is not checked for syntax.
-
If you post the database structure and how you want your query to be I might be able to help you build the query.