Geek asks and answers

Using ISNULL when adding NULL to varchar

During experiments with MSSQL, I came across some kind of behavior that I can not explain.

I watched what happens with a NULL value when it is added to varchar, and when I make the following request:

SELECT
   ISNULL(NULL + ' ', 'test')

I get the result of 'te'. Similarly, if I change the word test for any other word, I get only the first two letters. If I increase the number of spaces in + '', I get extra letters in my result (so NULL + '[two spaces]' gives me tes'). Any ideas what is going on?

If I declare a variable and set it to NULL, for example.

DECLARE @testnull AS varchar(32)

SET @testnull = NULL

SELECT
ISNULL(@testnull + ' ', 'test')

then I get the result of 'test' (as expected).

+5
source share
1 answer

COALESCE. ISNULL NON-NULL, , , VARCHAR(1) NULL, VARCHAR(2) ( - NULL , SQL Server ). varchar 1, - 30. , .

SELECT
 ISNULL(NULL + ' ', 'test'), COALESCE(NULL + ' ', 'test');

:

----  ----
te    test

:

SELECT
   x = ISNULL(NULL + ' ', 'test')
   INTO #a1;

SELECT
   x = COALESCE(NULL + ' ', 'test')
   INTO #a2;

SELECT LEFT(t.name, 3), TYPE_NAME(c.user_type_id), max_length
FROM tempdb.sys.columns AS c
  INNER JOIN tempdb.sys.tables AS t
  ON c.[object_id] = t.[object_id]
  WHERE t.name LIKE '#a[1-2]%';

:

---  -------  ----
#a1  varchar     2
#a2  varchar     4

COALESCE ISNULL. , ( ) :

http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

+7

More articles:


All Articles