Fields with Empty Strings 1

[sds814]

I need to know which fields have an empty string in them. So I tried it with one of the fields and it works. My manager thinks that with different data-types it would not, but I'm pretty confident that this will do the trick regardless of what data type the field is:

SELECT CM.ControlID, CM.LoanNumber 
FROM tblControlMaster CM 
WHERE CASE WHEN NULLIF(CM.LoanNumber, '') IS NULL THEN 0 END = 0
ORDER BY CM.ControlId

So whos correct?

Thanks.

 

[k01]

Hi,

If LoanNumber has no null,it will work.
But if yes, SQL should be modified like this.

SELECT
   CM.ControlID
  ,CM.LoanNumber
FROM
  tblControlMaster CM
WHERE
  ISNULL(CM.LoanNumber'')=''
ORDER BY 
  CM.ControlId

Koichi

http://aboutabi.blogspot.com/

(SQLServer & Cognos Tips)

 

[gmmastros]

>> I'm pretty confident that this will do the trick regardless of what data type the field is.

I do not agree with you. Let me explain....

If your data type is varchar (or any other type of string), your code will return rows where the value of the column is NULL or an empty string. So, you're probably thinking, "It works, so I'm done." Unfortunately, working, and working fast are completely separate. Your code is not [google]sargable[/google], which means that it will perform slower than it needs to. Often times, you can re-write a non-sargable query in to a sargable query. Like this...

[COLOR=blue]SELECT[/color] CM.ControlID, CM.LoanNumber
[COLOR=blue]FROM[/color]   tblControlMaster CM
[COLOR=blue]WHERE[/color]  (LoanNumber [COLOR=blue]Is[/color] NULL Or LoanNumber = [COLOR=red]''[/color])
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] CM.ControlId

If your table has an index on LoanNumber, the query I just showed you will use the index to identify the records faster than your query will. With small tables, you may not notice a difference in performance, but as the table grows, it will become more obvious.

Generally speaking, it is better for performance if you do NOT use any functions or calculations on columns when you can avoid it. In your query, you used the NULLIF function. In k01's query, the IsNull function was used. By separating the where clause condition in to 2 conditions (like I showed), there is no function on the column.

Alternatively, you could use the knowledge that NULL values do not compare to other values and construct your query like this...

[COLOR=blue]SELECT[/color] CM.ControlID, CM.LoanNumber
[COLOR=blue]FROM[/color]   tblControlMaster CM
[COLOR=blue]WHERE[/color]  LoanNumber > [COLOR=red]''[/color]
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] CM.ControlId

You will get the same results, and the query will execute faster.

Now, in your original post, you mentioned other data types. All the queries so far operate as though the data type is a string. This will NOT work for all data types, but will work for some.

Ex:

Declare @Temp [!]int[/!]
Set @Temp = 0

Select 1
Where  @Temp = ''

This appears to work, but only because SQL Server converts an empty string to 0.

Declare @Temp [!]Numeric(10,2)[/!]
Set @Temp = 12.34

Select 1
Where  @Temp = ''

[red]Error converting data type varchar to numeric.[/red]

Declare @Temp [!]UniqueIdentifier[/!]
Set @Temp = NULL

Select 1
Where  @Temp = ''

[red]Syntax error converting from a character string to uniqueidentifier.[/red]

As you can see, this code does not work for all data types.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

George,
I read posts just to pick up information. You have given me much to think about. Have a star.
Thanks for your explanation to sds814.

djj

 

[ESquared]

Note: NullIf expands to a CASE statement. So let's see what that looks like:

NULLIF(CM.LoanNumber, '')
--->
CASE WHEN CM.LoanNumber = '' THEN NULL ELSE CM.LoanNumber END

put that in your original query:

SELECT CM.ControlID, CM.LoanNumber 
FROM tblControlMaster CM 
WHERE
   CASE
   WHEN CASE WHEN CM.LoanNumber = '' THEN NULL ELSE CM.LoanNumber END IS NULL
      THEN 0
   END
   = 0
ORDER BY CM.ControlId

I hope that helps illustrate how George's solution is the right one. Just use an OR statement.

As for your original issue with your manager, you're both not quite correct.

Your solution will work with any character data type or any data type implicitly convertable to or from character. But it will break with any data type not implicitly convertable to or from character:

create table #tblControlMaster (LoanNumber uniqueidentifier)
insert #tblcontrolmaster select null
insert #tblcontrolmaster select 0x443322116655887799aabbccddeeff00
select * from #tblcontrolmaster 

SELECT *
FROM #tblControlMaster CM 
WHERE CASE WHEN NULLIF(CM.LoanNumber, '') IS NULL THEN 0 END = 0
GO
drop table #tblControlMaster

There is a help page in SQL Server Books Online that shows a table of the explicit and implicit conversions possible between all data types.