ISNULL in SQL Server 2008

Question

Today I ran into an odd problem with the ISNULL function in SQL

I have a table containing customer information. I need to display the full name combining the first, middle starting and last name. In the table I know, Middle Initial is a nullable column.

So, I used the ISNULL function in SQL to return the Average Initial, if it is not null, which works fine, but I used the ISNULL function with an expression that is lower, ideally I should not work if MI is null, but interesting it worked.

SELECT FirstName + ' ' + LastName + ' '+ ISNULL(MI + '.', '') As MiddleInitial FROM CS_tblMaster WHERE CustomerNo = 2627240 

Above SOL Query should return me MiddleInitial as "." when MiddleInitial is null, but it returned as an empty string.

So, I wrote another query as shown below.

 SELECT (MI + '.') AS MiddleInitial FROM CS_tblMaster WHERE CustomerNo = 2627240 

which is again set to NULL

Somehow, when you concatenate a string with a NULL value, it returns null. I would like to understand this implementation.

Can someone help

Answer 1

- Original expression

 SELECT FirstName + ' ' + LastName + ' '+ ISNULL(MI + '.', '') As MiddleInitial FROM CS_tblMaster WHERE CustomerNo = 2627240 

- My original answer (edited)

 SELECT FirstName + ' ' + LastName + ' '+ (ISNULL(MI, '') + '.') As MiddleInitial FROM CS_tblMaster WHERE CustomerNo = 2627240 

In this case, SQL will first check if MI is Null and use MI if Not Null, or use an empty string if it is. He then combines this result, which is now Null, with the period.

- Final answer

 SELECT FirstName + ' ' + LastName + ' ' + CASE WHEN MI IS NULL THEN '' ELSE MI + '.' END As MiddleInitial FROM CS_tblMaster WHERE CustomerNo = 2627240 

@Satish, not sure if you feel that you have an answer since you didn’t choose it, and I apologize if my answer was short and quick. Having seen all the answers, I realized that I did not think about your question when I first saw it.

To answer “I would like to understand this implementation,” Nulls is a very special meaning in SQL. Not an empty string, not spaces, not zeros. They literally mean "nothing." You can check them out if this can be something empty. But you cannot do things with Nulls. So 57 + Null = Null. 'Mary' + Null = Null. ((12 * 37) +568) / Null = Null. Max () “Albert”, “Maria”, “Zero” and “Zek” are “Zero”. This article http://en.wikipedia.org/wiki/Null_(SQL) can help with a decent description in the Null Propagation section.

The Isnull function is not so much a test for Null as a processing method. Therefore, to check if something is Null, you should use ColumnName Is Null or ColumnName, not Null in your Select. What Isnull (MI, ``) says: I want a value, if the MI value is not null, then I want MI, otherwise I want an empty string.

Continuing, I'm not sure I initially understood what you were actually trying to do. If you tried to get a period when the average starting number was zero, then my original answer and most others will work for you. But I think you are probably trying to say: “If I have an average starting level, then I need an average starting period, followed by a period. If I don’t have an average starting level, I don’t want anything: Alberto S. Santaballa "Alberto Santaballa, never Alberto. Santaballa." If so, then use the final report in the edited answer.

@Zec, thanks for editing. A typo was another product of typing too fast !: - /

Answer 2

If you want to put a period if MI is null, use this: (you just have '.' In the wrong place)

 SELECT FirstName + ' ' + LastName + ' '+ ISNULL(MI, '.') As MiddleInitial FROM CS_tblMaster WHERE CustomerNo = 2627240 

Answer 3

If you concatenate zero, you get zero. Take the spell outside. Isnull (MI, '') + '.'

Answer 4

Will something like this work for you ...

  SELECT FirstName + ' ' + LastName + ' '+ CASE WHEN MI is null then '.' else MI END As MiddleInitial FROM CS_tblMaster WHERE CustomerNo = 2627240 END 

Answer 5

brothers, I think you have a misunderstanding isnull function

 ISNULL([Columns To Check],[Replaced String]) SELECT FirstName + ' ' + LastName + ' '+ ISNULL(MI + '.', '') As MiddleInitial FROM CS_tblMaster WHERE CustomerNo = 2627240 

The ISNULL function refers to the column you want to check to be null, then replace it with other rows

in your request, you are trying to replace ur MI + '.' columns to an empty row, hope my explanation helps u up =)

Answer 6

How about NULLIF:

SELECT FirstName + '' + LastName + '' + ISNULL ((NULLIF (MI + '.', '.'), '') Like MiddleInitial FROM CS_tblMaster WHERE CustomerNo = 2627240