-2

I'm trying to handle a blank or '' value to change to NULL while doing a simple select, but it is not working.

select distinct case when [Manager]='' then null else [Manager] end as Mgr from Table
where [Manager] in ('Abc', 'bas', 'xyz', 'pqr', '')

Is there another way?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
ivric
  • 109
  • 1
  • 6
  • 24
  • 2
    Please show the incorrect results you are getting. – Dale K Nov 01 '18 at 20:01
  • Do you simply want to eliminate `''` and `NULL` from the output of your `DISTINCT` query? – Sergey Kalinichenko Nov 01 '18 at 20:03
  • 2
    missing a TRIM() maybe? – jean Nov 01 '18 at 20:14
  • 2
    Your code does what you state you want it to. I am guessing you have some other white space characters in your actual data. – Sean Lange Nov 01 '18 at 20:18
  • 1
    The comments on one of the answers is right. We need DDL and DML that replicates this issue. Clearly there is more to this than the the little we've been given let's on. – Thom A Nov 01 '18 at 21:24
  • If the problem is _variegated whitespace_, e.g. blanks, tabs, non-breaking spaces, ..., then [this](https://stackoverflow.com/questions/35245812/whats-a-good-way-to-trim-all-whitespace-characters-from-a-string-in-t-sql-witho/35247507#35247507) answer may help. – HABO Nov 02 '18 at 01:17
  • 1
    Yeah. You don't have a query problem. You have a data problem. – Eric Brandt Nov 02 '18 at 03:50

2 Answers2

0

I guess, you also need to check null values with OR [Manager] IS NULL

select 
    distinct case when [Manager]='' then null else [Manager] end as Mgr 
from Table
where 
    [Manager] in ('Abc', 'bas', 'xyz', 'pqr', '')
    OR [Manager] IS NULL
Serkan Arslan
  • 13,158
  • 4
  • 29
  • 44
  • Why wouldn't you just use `WHERE ISNULL([Manager]),'') IN ('Abc', 'bas', 'xyz', 'pqr', '')`? – digital.aaron Nov 02 '18 at 18:42
  • 1
    because of performance. if any index exist on manager column, isnull can make the index useless. – Serkan Arslan Nov 02 '18 at 19:18
  • Good point. I found this over on dba.stackexchange.com via a Brent Ozar blog post. It has some interesting info on this subject: https://dba.stackexchange.com/questions/168276/what-are-different-ways-to-replace-isnull-in-a-where-clause-that-uses-only-lit – digital.aaron Nov 02 '18 at 19:33
0

Honestly there's a million ways to do it, and someone did point out correctly that the function in the where clause eliminates the use of an index.

But what is happening in my query is I am removing any whitespace surrounding the field (the two trims), then the isnull says, if you find a null value then set it to ''. I do this to only have one statement, alternatively you could get rid of the isnull and say "Or [manager] is null". The select is saying trim manager and if it's blank (nullif) set it to null which is the expected outcome if I understood correctly.

SELECT 
    DISTINCT NULLIF(LTRIM(RTRIM([manager])),'') AS Mgr
FROM
    Table
WHERE
    ISNULL(LTRIM(RTRIM([manager])),'') IN ('Abc','bas','xyz','pqr','')
digital.aaron
  • 5,435
  • 2
  • 24
  • 43
Josh
  • 414
  • 6
  • 14
  • While your use of `NULLIF` is correct here, you could expand your answer a little to explain why your answer is correct. – digital.aaron Nov 02 '18 at 18:40
  • Honestly there's a million ways to do it, and someone did point out correctly that the function in the where clause eliminates the use of an index. But what is happening is I am removing any whitespace surrounding the field (the two trims), then the isnull says, if you find a null value then set it to ''. I do this to only have one statement, alternatively you could get rid of the isnull and say "Or [manager] is null". The select is saying trim manager and if it's blank (nullif) set it to null which is the expected outcome if I understood correctly. – Josh Nov 02 '18 at 19:39