Stack Overflow
Stack Overflow

Questions tagged [isnull]

`ISNULL` is a proprietary SQL function that replaces NULL with the specified replacement value.

ISNULL is a SQL function that replaces NULL with the specified replacement value.

The ANSI/ISO SQL standard function COALESCE serves the same purpose, and is widely implemented.

Reference

MSDN Article

706 questions
8
votes
4 answers

MySQL - Selecting rows with null columns

How can I select any row that contains empty or null column? I'm trying to run a check on my table, in which I want to see if any of my rows contain a column that doesn't hold a value.. example table: demo +----+------+------+ | ID | col1 | col2…
Zalaboza
  • 8,899
  • 16
  • 77
  • 142
8
votes
4 answers

Is Sql Server's ISNULL() function lazy/short-circuited?

TIs ISNULL() a lazy function? That is, if i code something like the following: SELECT ISNULL(MYFIELD, getMyFunction()) FROM MYTABLE will it always evaluate getMyFunction() or will it only evaluate it in the case where MYFIELD is actually null?
eidylon
  • 7,068
  • 20
  • 75
  • 118
7
votes
3 answers

Sargable queries using ISNULL in TSQL

I'm looking to prevent non-sargable expressions in my queries, which is the better way to check for a null condition? AND c.Account IS NOT NULL AND c.Account <> '' or AND ISNULL(c.Account,'') <> '' It dawned on me to point out that Account is…
jcolebrand
  • 15,889
  • 12
  • 75
  • 121
7
votes
11 answers

How does Left Join / IS NULL eliminate records which are there in one table and not in the other?

I am having a tough time to understand why does LEFT JOIN / IS NULL eliminate records which are there in one table and not in the other. Here is an example SELECT l.id, l.value FROM t_left l LEFT JOIN t_right r ON r.value = l.value WHERE …
StrugglingCoder
  • 4,781
  • 16
  • 69
  • 103
7
votes
2 answers

Checking to see if a an Enum is null or empty

I am trying to validate a json that is being sent into my controller and I am using the BindingResult way and I am able to validate strings and everything else fine as normal. But im not sure how to check if an Enum is empty or null.
Shaun
  • 157
  • 1
  • 5
  • 16
7
votes
1 answer

IsNull in VB6 and VB.net

I have a code - strTest=" " IsNull(Trim(strTest)) It returns False in VB6 . I write this code to VB.net but IsNull(Trim(strTest)) returns True . So, IsNull(Trim(" ")) in VB6 = ?? in VB.net Thank you.
nnnn
  • 1,041
  • 3
  • 18
  • 35
7
votes
2 answers

Many-column UPDATE-JOIN with many-ISNULL takes a long time?

We have a stored proceudre in our database that updates a table by joining 2 tables on 30 columns with a where condition. The SQL Is in the general format of: UPDATE Target SET col1 = Source.col1 INNER JOIN Source on ISNULL(Target.Col2, '') =…
Chad
  • 23,658
  • 51
  • 191
  • 321
6
votes
1 answer

NOT IN implementation of Presto v.s Spark SQL

I got a very simple query which shows significant performance difference when running on Spark SQL and Presto (3 hrs v.s 3 mins) in the same hardware. SELECT field FROM test1 WHERE field NOT IN (SELECT field FROM test2) After some research of…
Bostonian
  • 615
  • 7
  • 16
6
votes
1 answer

Assigning values to Null throwing error on decimal type

Using SQL Server 2016 I am currently supporting an old import routine that wraps lots of dynamic columns with ISNULL to tidy up missing data. The code is generated in dynamic SQL and provides a string in the format of ISNULL(ColumnName, '')…
Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
6
votes
1 answer

Convert INT column values to an empty string using ISNULL

I need to convert column ID of INT data type to a empty string ['']. I should not modify the source column data type, but need to convert it in my transformation in the other table. The ID column is "nullable" as it has null in it.This is my code.…
Data Engineer
  • 795
  • 16
  • 41
6
votes
3 answers

SQL Server NULL Integer to Empty String using ISNULL

My curiosity always gets the best of me and I've searched online for an explanation to this and came up with nothing (could be because I didn't use the right terms.) Can someone please explain why SQL Server returns a value of zero (0) when the…
Ahz
  • 361
  • 1
  • 2
  • 6
6
votes
3 answers

Can using isnull in a where statement cause problems with using indexes?

I have a query like the following: SELECT t1.v3, t2.v2 FROM t1 INNER JOIN t2 ON t1.v1 = t2.v1 WHERE ISNULL(t1.DeleteFlag,'N') = 'N' I have an index in place that I think should result in there being an Index Seek for the = 'N' part but instead I…
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
6
votes
3 answers

Nullable Field and SQL Is Null Issue

There are tons of Q&A on stackoverflow related to my question , but I cannot deduce the reasoning of the issue and the solution that works best in this scenario; So I've a method that allows you to pass a parentID, and based on the value records…
Kunal
  • 1,913
  • 6
  • 29
  • 45
6
votes
3 answers

ANSI equivalent of IS NULL

I am trying to find the ANSI way to write the T-SQL 'IS NULL'. (corrected, was 'IN NULL') Some posts on the internet say you can use coalesce to make it work like 'IS NULL' The reason I like to do this: portable code. And the query must return the…
chihwah li
  • 85
  • 1
  • 1
  • 4
6
votes
1 answer

How do I check whether a field contains null value? - pymongo

Sometimes my document has a field that has null values. How do I check whether the value inside a field is a null string? How do I iterate through all documents and delete the field of the document if it has a null string value? Should I delete…
alvas
  • 115,346
  • 109
  • 446
  • 738
1 2
3
…
47 48