Menu
Today I was testing something at work place and came across this one
Case 1:
Declare @a nvarchar(20)
Set @a = null
Select IsNull(LTrim(RTrim(Lower(@a))), -1)
Case 2:
Select IsNull(LTrim(RTrim(Lower(null))), -1)
The result in case 1 is -1 but * in case 2
I was expecting same results in both cases. Any reason?
933
Returns a Boolean value that indicates whether an expression contains no valid data (Null). Syntax. IsNull ( expression ) The required expressionargument is a Variant containing a numeric expression or string expression.
The isNull operator checks a string and returns a boolean value: true if the string is null, or false if the string is not null. The isEmpty operator checks if a string contains no characters and is only whitespace. The isBlank operator checks if a string contains no characters, is only whitespace, and is null.
ISNULL replaced the Oracle NVL function in the SQL server. When an expression in SQL server is NULL, the ISNULL function allows you to return an alternative value for the null.
COALESCE and ISNULL advantage that COALESCE has over ISNULL is that it supports more than two inputs, whereas ISNULL supports only two. Another advantage of COALESCE is that it's a standard function (namely, defined by the ISO/ANSI SQL standards), whereas ISNULL is T-SQL–specific.
Without the declaration of data type, null in this case is declared as varchar(1). You can observe this by selecting the results into a #temp table:
Select IsNull(LTrim(RTrim(Lower(null))), -1) as x INTO #x;
EXEC tempdb..sp_help '#x';
Among the results you'll see:
Column_name Type Length
----------- ------- ------
x varchar 1
Since -1 can't fit in a varchar(1), you are getting * as output. This is similar to:
SELECT CONVERT(VARCHAR(1), -1);
If you want to collapse to a string, then I suggest enclosing the integer in single quotes so there is no confusion caused by integer <-> string conversions that aren't intended:
SELECT CONVERT(VARCHAR(1), '-1'); -- yields "-"
SELECT CONVERT(VARCHAR(30), '-1'); -- yields "-1"
I would not make any assumptions about how SQL Server will handle a "value" explicitly provided as null, especially when complex expressions make it difficult to predict which evaluation rules might trump data type precedence.
122
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
