Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pinto
Aged Yak Warrior
590 Posts |
 Posted - 2005-07-05 : 05:49:56
|
| I have a SQL table which was once an Access table and has been imported into SQL. Some of the fields have null in them. Could someone tell me the syntax of a SQL update query to change all the nulls to a zero length string. I can then run it field by field where appropriate.TIA |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-05 : 05:54:49
|
| [code]UPDATE MyTableSET MyColumnA = COALESCE(MyColumnA, ''), MyColumnB = COALESCE(MyColumnB, ''), ...[/code]but I have to say this is almost certainly NOT a good idea.Kristen |
 |
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2005-07-05 : 06:03:26
|
| I am having problems with the following (I have 3 Case statements)which drives a crystal report. My User enter criteria on a asp.page (or leaves fields blank for everything). If for example Category has null in it the line & "{tblSL_Calls.Category} like '*" & strCategory & "*' and" _ doesn't return the record. Is there another solution as any of the fields could be null or blank except for the dates. The null is left from Access days, all SQL records have zero length string. Case 1 'Outstanding Calls req.ReportState.RecordSelectionFormula = "IsNull({tblSL_Calls.Completed_time})and IsNull({tblSL_Calls.Cancelled})and" _ & "{tblSL_Calls.Division} Like '*" & strDiv & "*' and" _ & "{tblSL_Calls.Service} like '*" & strService & "*' and" _ & "{tblSL_Calls.Category} like '*" & strCategory & "*' and" _ & "{tblSL_Calls.Area} like '*" & strArea & "*'" _ & " and {tblSL_Calls.Call_date} in DateTime(" & SYear & "," & SMonth & "," & SDay & ", 00, 00, 00) to DateTime(" & EYear & "," & EMonth & "," & EDay & ", 00, 00, 00)" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-05 : 08:49:26
|
I'd consider first of all whether NULL is an appropriate value.If the value is UNKNOWN then NULL seems appropriate to me.Address line 4, say, might legitimately be "Blank" if the rest of the address lines are "known".Empty/blank fields in SQL can be a bit of a problem - particularly with older versions of SQL, and its sometimes better to treat them as NULL - no hard & fast rule though.Certainly if a user has NEVER provided a value for a field I would want to be storing NULL in it - we don't YET know the value of that field.An optional field which was on a data entry form, but which the user choose not to answer (e.g. "What is your mother's name") is probably NULL - we don't know her name - her name was certainly NOT ""  )If a field can NEVER be NULL, then the database should be set so that column cannot accept NULLs. Also, you could put a Default Value on such a column.As you have said"{tblSL_Calls.Category} like '*" & strCategory & "*' AND ..."will not match NULLs - they fail all such tests; both:tblSL_Calls.Category like '%FOO%'andtblSL_Calls.Category NOT like '%FOO%'will exclude rows where tblSL_Calls.Category IS NULL.So, you will need to explicitly check for NULLs. In general terms you need to use IS NULL or IS NOT NULL together with any other test. For example:tblSL_Calls.Category IS NULL OR tblSL_Calls.Category like '%FOO%'Sorry, bit long winded, getting there!So, in your example where you want the user to include "Blank gives me everything" you should do:"(' & strCategory & ' = '' OR {tblSL_Calls.Category} like '*" & strCategory & "*') AND ..."this will be "True" if strCategory is a blank string, OR the Category contains strCategory. If the user provides a value for strCategory then NULL/BLANK ones will NEVER be matched - the same as you ahve at present.Note that in SQL for wildcards you need to use "%" rather than "*" (Crystal / Access may do that silently for you)Kristen |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-07-05 : 08:54:29
|
| There is a fundamental problem with your technique: If Category can be NULL, then how is the parameter passed in to indicate you wish to return only Null values for category? Because you have defined Null as meaning "do not filter on this column".In general, "Enter a value for XX or NULL to return all" doesn't work so well if column XX has null values.- Jeff |
|
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2005-07-05 : 11:18:52
|
| If a value is entered I want it included inthe search criteria. if it is left blank then that value can be "Red" <null> or ''My imported records with no value defaulted to <null>. My new records have '' if there is no value entered. I have changed all <null> values to '' and it works, though I accept Kristen's point. |
|
|
|
Crito
Starting Member
40 Posts |
Posted - 2005-07-05 : 23:26:29
|
| Only time I like using nulls is non-identifying foreign key relationships. IS NULL/IS NOT NULL quickly tells you whether the relationship exists or not... it would have to exist in an identifying foreign key. Three-value logic doesn't work well for me. ;)----------------------------------Gun for hire, have horse, will travel. |
|
|
|
|
|
|
|
|
Replace null with zero length string