79299

ISNULL returns 0 for a hardcoded column with NULL value

Question:

Why this statement return 0 instead of empty string? I am actually using a view which select statement containg hardcoded column return NULL. While I am trying to check if its a NULL, it actually returns 0.

SELECT ISNULL((SELECT NULL as Col), '')

Any suggestion or help would be appreciated...

<a href="http://i.stack.imgur.com/42Vex.png" rel="nofollow">snapshot</a>

Answer1:

NULL does not have a clear type, but in a plain SELECT NULL, it gets returned as type int. When you have an expression involving an int and a char(N), int wins, the char(N) value gets converted to int, not the other way around. To make things more confusing, '' happens to be convertible to int without any problem, and the result of the conversion is 0.

SELECT ISNULL((SELECT CAST(NULL AS char(1)) AS col), '') should return an empty string.

Recommend

  • Shorthand for wrapping a swift variable in an optional?
  • How do I Suppress Warnings in CheckStyle? [duplicate]
  • Removing duplicate rows by adding column value
  • Added String to ArrayList not shown / not updated [duplicate]
  • How to use webview as a browser in android application?
  • Calling SaveAsync method sends email to attendees for existing appointment?
  • Apprequest of Android Facebook sdk 3.0
  • Efficient Google PubSub Publishing
  • LIBSODIUM decrypt data inside mysql query like did with AES_DECRYPT
  • Keyboard Extension Crash on Device
  • How to set a dynamic form fields to dirty or touched with angular?
  • DocuSign API Replace template document but keep fields
  • Hide legend for a single geom in ggplot2
  • How to delete first 7 characters of folder name by using batch script?
  • Eric5: The OK button of 'new project' dialog is disable
  • Python sum values in tuple in a list in a dictionary?
  • How to sort by Lucene.Net field and ignore common stop words such as 'a' and 'the
  • Why am I getting an Argument exception when creating event handler dynamically?
  • $this->a->b->c->d calling methods from a superclass in php
  • How to convert days into months using datetime in Python3?
  • How to create OLE Automation to be used with ClearExplorer
  • Protractor Page objects - TypeError: Object # has no method 'methodName'
  • ASP.NET GridView throws: The version of SQL Server in use does not support datatype 'date'
  • How can I filter an array of dictionaries in 'updateSearchResultsForSearchController' to s
  • Get name of days between two date in ios?
  • How to add html image in to velocity template file to send email?
  • Slice assignment to tensorflow variable
  • What is the difference between dynamically creating a script tag and statically embed a script tag?
  • Regex not working in java 1.5
  • Year over Year Stats from a Crossfilter Dataset
  • xpath assertion failure with dynamic xpath
  • How to use Kaminari pagination gem with Sinatra and Mongoid?
  • Why is ordered choice in pyparsing failing for my use case?
  • Neo4j…how to get a visual representation of my data?
  • Using redis as an LRU cache for postgres
  • Bad automatic Triangulation with Mayavi for coloring a surface known only by its corner
  • Background transfer download task failed when app was closed
  • Firebase: How to read from external DB?
  • Write to .csv file with PHP (Commas in Data Error)
  • How to mutate multiple variables without repeating codes?