Strange SQL Query doesn't accept NULL
-
I am using the like statement in searching forms. It works as long as I don't have a column in the SQL DB which haven't NULL as entry. For example : A car and it's color Porsche Grey Honda NULL BMW Black SELECT .... WHERE TYP LIKE 'Porsche%' AND COLOR LIKE '%' works ! Whole Porsche line SELECT .... WHERE TYP LIKE '%' AND COLOR LIKE 'Black' works too. Whole BMWline but both ... SELECT .... WHERE TYP LIKE 'Honda%' AND COLOR LIKE '%' SELECT .... WHERE TYP LIKE 'Honda%' don't work because Column Color for Honda has a NULL entry. Confusing, isn't it ? Is this a bug, or something what can be solved ?
-
I am using the like statement in searching forms. It works as long as I don't have a column in the SQL DB which haven't NULL as entry. For example : A car and it's color Porsche Grey Honda NULL BMW Black SELECT .... WHERE TYP LIKE 'Porsche%' AND COLOR LIKE '%' works ! Whole Porsche line SELECT .... WHERE TYP LIKE '%' AND COLOR LIKE 'Black' works too. Whole BMWline but both ... SELECT .... WHERE TYP LIKE 'Honda%' AND COLOR LIKE '%' SELECT .... WHERE TYP LIKE 'Honda%' don't work because Column Color for Honda has a NULL entry. Confusing, isn't it ? Is this a bug, or something what can be solved ?
No, not a bug; it can be confusing though. NULL means "I don't know". You're asking for any colour, but SQL is saying it can't tell if the Honda has a colour (hey, a transparent car!!). You could try: SELECT ... WHERE TYP LIKE 'Honda%' AND (COLOR LIKE '%' OR COLOR IS NULL) I don't know why you're not getting anything for the case where the TYP is specified but not the colour, since that shouldn't cause a problem. Are you doing a single table query, or is it more complex? If the latter, you might not have results for a different reason, like there being no suitable data (no data in one of the other tables to join, for instance) Steve S Developer for hire
-
No, not a bug; it can be confusing though. NULL means "I don't know". You're asking for any colour, but SQL is saying it can't tell if the Honda has a colour (hey, a transparent car!!). You could try: SELECT ... WHERE TYP LIKE 'Honda%' AND (COLOR LIKE '%' OR COLOR IS NULL) I don't know why you're not getting anything for the case where the TYP is specified but not the colour, since that shouldn't cause a problem. Are you doing a single table query, or is it more complex? If the latter, you might not have results for a different reason, like there being no suitable data (no data in one of the other tables to join, for instance) Steve S Developer for hire
Hi, I don't know why you're not getting anything for the case where the TYP is specified but not the colour, since that shouldn't cause a problem. Because there are cases in life where you have to accept nulls. Beside your response I have found a further solution. Instead TYP I have to write CARS.TYP and CARS.COLOR CARS is the Table ... and finally it works with the table prefix ;) Unbelievable ...
-
Hi, I don't know why you're not getting anything for the case where the TYP is specified but not the colour, since that shouldn't cause a problem. Because there are cases in life where you have to accept nulls. Beside your response I have found a further solution. Instead TYP I have to write CARS.TYP and CARS.COLOR CARS is the Table ... and finally it works with the table prefix ;) Unbelievable ...
Vector7 wrote: Because there are cases in life where you have to accept nulls. Yes, I know that; I've been using RDBMS for over 20 years :) What I was trying to point out was that if you perform a syntactically correct select statement SELECT * FROM CARS WHERE TYP = 'Honda%' it should return rows, regardless of whether any column in the table has a NULL value (apart from TYP, obviously!). (Your original post said it wasn't working). If that doesn't work, it suggests issues other than NULL handling. Since you 'fixed' it by specifying a table prefix, that suggests the parser is otherwise being slightly confused by the names. What version and SP of SQL Server are you running? Steve S Developer for hire