CONCAT function ignores NULLs: SELECT CONCAT(LEFT(LastName, 1), ‘,’ , LEFT(FirstName, 1), ‘ ‘ + LEFT(MiddleName, 1)) theNameWithInitials FROM myTable If you use CONCAT, you will not need to worry about whether LEFT(X) is null or not.
Tag: null
The Null Conditional Operator in C# (?.)
The null conditional operator (?.) is colloquially referred to as the “Elvis operator” because of its resemblance to a pair of dark eyes under a large quiff of hair. The null conditional is a form of a member access operator (the .). Here’s a simplified explanation for the null conditional operator: The expression A?.B evaluates to B if the left operand (A) […]
The Null-Coalescing Operators in C# (?? and ??=)
The null-coalescing operator ?? returns the value of its left-hand operand if it isn’t null; otherwise, it evaluates the right-hand operand and returns its result. The ?? operator doesn’t evaluate its right-hand operand if the left-hand operand evaluates to non-null. int? myValue = null; int result = myValue ?? -1; //result = -1; Syntax: p ?? q Here, p is […]
SQL Tip: “NULL” should not be compared directly
“NULL” is never equal to anything, even itself. Therefore comparisons using equality operators will always return False, even when the value actually IS NULL. For that reason, comparison operators should never be used to make comparisons with NULL; IS NULL and IS NOT NULL should be used instead. Bad example: UPDATE books SET title = ‘unknown’ WHERE title = NULL — Noncompliant […]
How to create the ShowBlanksValue and ShowNonBlanksValue items in Devex Grid
By design, GridViewDataComboBoxColumn does not render (Blank) and (NonBlank) items if the HeaderFilterMode property is set to CheckedList. However, you can add these items in the HeaderFilterFillItems event handler. Just call the FilterValue.CreateShowBlanksValue and FilterValue.CreateShowNonBlanksValue methods. <dx:ASPxGridView ID=”grid” runat=”server” AutoGenerateColumns=”False” DataSourceID=”dsCategories” KeyFieldName=”CategoryID” OnHeaderFilterFillItems=”grid_HeaderFilterFillItems”> <Columns> <dx:GridViewDataTextColumn FieldName=”CategoryID”> </dx:GridViewDataTextColumn> <dx:GridViewDataTextColumn FieldName=”Description”> </dx:GridViewDataTextColumn> <dx:GridViewDataComboBoxColumn FieldName=”CategoryNameNull” Caption=”Category Name”> <Settings HeaderFilterMode=”CheckedList” /> <PropertiesComboBox DataSourceID=”dsCategories” ValueField=”CategoryNameNull” TextField=”CategoryNameNull” /> </dx:GridViewDataComboBoxColumn> </Columns> […]
How to check for ‘IS NOT NULL’ And ‘IS NOT EMPTY’ string in SQL
If you only want to match N” as an empty string SELECT COLUMN FROM TABLE WHERE DATALENGTH(COLUMN) > 0 If you want to count any string consisting entirely of spaces as empty SELECT COLUMN FROM TABLE WHERE COLUMN <> ” If you want to use DATALENGTH for any string consisting entirely of spaces then you […]
Default Values for Data Types in C#
Table below shows the default value for the different predefined data types. Type Default sbyte, byte, short, ushort, int, uint, long, ulong 0 char ‘\x0000’ float 0.0f double 0.0d decimal 0.0m bool false object null string null As you can see, for the integral value types, the default value is zero. The default value for […]
How to Solve The ‘Object reference not set to an instance of an object’ error in Batch Edit mode for ASPxGridView
The issue occurs in Batch Edit Mode, because in this mode an empty invisible row is created and used for further grid editing. Since the Eval method returns a value type of an object, your attempt to call the ToString() method to the null value can lead to an exception. If you remove this conversion and use <%# […]
Add blank item at top of dropdownlist
drpList.Items.Insert(0, new ListItem(String.Empty, String.Empty)); drpList.SelectedIndex = 0;
What does COALESCE do in SQL?
In SQL Server (Transact-SQL), the COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null. SELECT COALESCE(NULL, NULL, ‘ErcanOPAK.com’, NULL, ‘blog.ErcanOPAK.com’); Result: ‘ErcanOPAK.com’ SELECT COALESCE(NULL, ‘ErcanOPAK.com’, ‘blog.ErcanOPAK.com’); Result: ‘ErcanOPAK.com’ SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4); Result: 1