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
4
votes
4 answers

how to select rows with no null values (in any column) in SQL?

I have a table called table1 It has 100 columns: {col1, col2, ...,col100} I understand how to SELECT rows not containing null values in a specific column for instance col1: SELECT * FROM table1 WHERE col1 IS NOT NULL How do I SELECT all rows that…
conor
  • 1,131
  • 1
  • 15
  • 20
4
votes
2 answers

Is there a difference between IS NULL and IS NOT DISTINCT FROM NULL?

Is there a difference between value IS NULL and value IS NOT DISTINCT FROM NULL? Is it the same for each SQL dialect?
Art
  • 2,235
  • 18
  • 34
4
votes
3 answers

Removing NULL value in SQL JOIN and UNION Operators in SQL

[SOLVED] how to remove the NULL value only in the field provider_order only, in my case the table was using JOIN Table and UNION Here is my database schema and My SQL Query http://sqlfiddle.com/#!9/f77862/2 To clearly describe here i attach the…
Bodronoyo Pro
  • 43
  • 1
  • 5
4
votes
4 answers

Scala Check optional string is null or empty

I am a newbie to Scala and I want to learn how can I add null and empty check on an optional string? val myString : Option[String] if (null != myString) { myString .filter(localStr=> StringUtils.isEmpty(localStr)) .foreach(localStr=>…
Robin
  • 167
  • 1
  • 2
  • 8
4
votes
2 answers

ISNULL and Implicit datatype conversions in TSQL

I came across a somewhat weird behavior with data type conversions when using ISNULL. Look at this: PRINT CASE WHEN ISNULL('', 0) = 0 THEN 'true' ELSE 'false' END PRINT CASE WHEN ISNULL('', 0) = '' THEN 'true' ELSE 'false' END PRINT CASE WHEN…
Agent_K
  • 815
  • 8
  • 12
4
votes
2 answers

ISNULL erroneous return?

I have the following query: SELECT apps.Field4, ISNULL(apps.field4, '-1') FROM applications apps WHERE apps.OBJECT_ID = 1727847 AND ISNULL(apps.Field4, -1) = -1 apps.field4 is an integer, and no record has a value less than 0 for…
Developer Webs
  • 983
  • 9
  • 29
4
votes
1 answer

JPA: Left join not working with "is null" in where clause

I am using EclipseLink (2.4, 2.5 and 2.6) on a very simple project where I have a Department entity and each Department links to an Employee entity which is the manager of the Department. I am currently unable to make this simple query work: select…
Guillaume Polet
  • 47,259
  • 4
  • 83
  • 117
4
votes
1 answer

SQLServer update statement chokes on null value, even with isNull()

Please note that I've changed the names of the tables and fields to make this short and understandable. I have a query that, boiled down, comes to this: update destTable set destField = ( select top 1 isnull(s.sourceField, '') from sourceTable s…
Jerome P Mrozak
  • 1,907
  • 4
  • 21
  • 33
4
votes
4 answers

What's the meaning of the reverse entry (null === $value) when checking the value of the variable?

Possible Duplicate: PHP - reversed order in if statement Checking for null - what order? Examining Zend Framework found that they do all the variable checkings reverse way: public function setBootstrap($path, $class = null) { if (null ===…
Green
  • 28,742
  • 61
  • 158
  • 247
4
votes
4 answers

SQL: select all rows if parameter is null, else only select matching rows

I have a variable coming into a stored procedure. This variable can either have a value or be null. If the variable is null, I need to select all the rows in the table (some with NULL values, some with actual data). If the variable is not null, I…
Steve's a D
  • 3,801
  • 10
  • 39
  • 60
4
votes
3 answers

mysql - function like isnull() to check for zero-value

I'm looking for a function to check if a column has the value 0 that behaves like the isnull() function. This is what I've got so far: CONCAT( SUBSTRING_INDEX( SUBSTRING(textCol, 1, LOCATE(DATE_FORMAT(dateCol,'%d.%m.%Y …
simbabque
  • 53,749
  • 8
  • 73
  • 136
3
votes
5 answers

IS NOT NULL and ISNULL( str, NULL ) in WHERE clause

I have three tables (simplified here): recipients: recipientId, isGroup users: userId, first name, last name groups: groupid, groupname I want to retreive the first name / last name if the recipient is a user, and the group name if the recipient is…
Swati
  • 50,291
  • 4
  • 36
  • 53
3
votes
2 answers

In SQL, How to output "NULL" instead of " There are no results to be displayed" when there's no value to be exported

Using MySQL v8.0 right now. The question is: Write an SQL query to report the id and the salary of the second highest salary from the Employee table. If there is no second highest salary, the query should report null. My dummy data is: Create…
union77
  • 103
  • 7
3
votes
0 answers

Error AttributeError: ("'str' object has no attribute 'isnull'", 'occurred at index 0')

I am trying to run an apply lambda function on an object column. How can I check that row 3 below is a Nan value Example test scores: 100,10,Nan,50,10,Nan def add_grade(row): if row[test_scores].isnull(): return 'F' else: …
Rami Shehadah
  • 59
  • 5
3
votes
2 answers

Do any databases get NULL and "NULL" confused, or is it always an application design failure?

I've read a few articles like this one about a license plate value of NULL (about Droogie from DEF CON 27), including part of chapter three Little Data in the book Humble Pi by Matt Parker (talking about Steve Null), where storing a string value of…
Jim McKeeth
  • 38,225
  • 23
  • 120
  • 194
1 2 3
…
47 48