Solved

What does ISNULL do exactly?

Posted on 2004-09-16
18
986 Views
Last Modified: 2008-01-16
Hi,

I'm trying to translate a SQL Server application to work in MySql. The MySql server burps when I send it this statement:

SELECT * FROM MYTABLE WHERE MYFIELD=ISNULL(%s)

The %s can be a string containing 'sumting' or a a string containing the word NULL

I was wondering what does the command ISNULL do exactly.

I know that if I do this in MySQL:

SELECT * FROM MYTABLE WHERE MYFIELD IS NULL

then I lose the search for 'sumting'...

-- Bubba
0
Comment
Question by:bganoush
  • 8
  • 4
  • 2
  • +3
18 Comments
 
LVL 4

Expert Comment

by:Kaarthick
ID: 12074518
If you place the '%' symbol before 's' then it means there is some letters before 's' say for example the word hash can be searched like '%sh'.

If you want to search for a string 'sumting' then use the % as like 's%' it will work.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12074537
ISNULL(x, y)
is the same thing as

[ IF X IS NULL THEN Y ELSE X ]

if you want to either check for a value, or a NULL entry, then you could use it like this :

SELECT * FROM MYTABLE WHERE ISNULL(MYFIELD, 'NULL') = %s

a better performance though might be

SELECT * FROM MYTABLE WHERE MYFIELD = %s OR (MYFIELD IS NULL AND %s = 'NULL')
0
 
LVL 4

Expert Comment

by:Kaarthick
ID: 12074544
You refine  your Query like the below one,

SELECT * FROM MYTABLE WHERE MYFIELD IS NULL OR myfield like 's%'
0
 

Author Comment

by:bganoush
ID: 12074553


I'm so sorry.. I forgot something....

the SQL statement is :

SELECT * FROM MYTABLE WHERE MYFIELD=ISNULL(%s, '')

I forgot the '' part of the ISNULL command...

Also, I put "%s" to symbolize a stdio insertion... this is not what is in the actual SQL, but it is replaced by the string NULL or 'sumting'

-- Bubba
0
 

Author Comment

by:bganoush
ID: 12074581

So Bill,

Does your solution work for both SQL Server AND MySQL... it looks like a general solution?

-- Bubba
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12074591
This query
SELECT * FROM MYTABLE WHERE MYFIELD=ISNULL(%s)
could not possibly work in sql server

===========
ISNULL function takes 2 arguments.

ISNULL
Replaces NULL with the specified replacement value.

Syntax
ISNULL ( check_expression , replacement_value )

Arguments
check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.

Return Types
Returns the same type as check_expression.

Remarks
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.

Jay
0
 
LVL 9

Expert Comment

by:solution46
ID: 12074604
IsNull(a, b) checks the value of argument a. If it is NULL, IsNull returns the default value b.

For instance, if I had a field in a database that held a numeric value but was sometimes Null and I wanted a Sum of this column, I would use the following code...

SELECT Sum(my_field, 0) FROM...

This would substitute 0 whenever the field was Null and them Sum normally. Without the IsNull() function, this Sum would return Null (Null + anything = Null, roughly equivalent to Unknown + anything = Unknown).

I think the code you are after for your exaple is...
SELECT * FROM MYTABLE WHERE IsNull(MYFIELD, 'NULL')= 'NULL'.
... but I'm not quite sure what you were trying to achieve!


Another function that works in a similar way is Coalesce(). This takes a series of arguments and returns the first non Null value. For instance...
COALESCE([field1], [field2], 0)
In this case, if [field1] was Null but [field2] was 7, the function would return 7; if both [field1] and [field2] were Null, it would return 0.


Hope this helps,

s46.
0
 

Author Comment

by:bganoush
ID: 12074682


Wait a minute!!!

This is stupid... The code passes in either the string "NULL" or the string "'SUMTING'" (note the single quotes inside the double quotes)....

So instead of messing around with a special SQL statement, can't I just pass in "''" (two singles within two doubles) or "'SUMTING'" instead? Then my SQL would just look like this:

SELECT * from MYTABLE where MYFIELD=%s

Does that make sense or am I missing the point entirely?
0
 
LVL 9

Expert Comment

by:solution46
ID: 12074992
hmm...

searching for thre string NULL will only ever return claues that have the string NULL in them (not values that are Null).

Also, you really need to clarify what you mean by single and double quotes. SLQ Server uses  the single quote (') as a string delimiter; in your examples, do you mean ' as a delimiter or as part of a string? The same question applies to ".

Could you please specify EXACTLY what you are trying to achive, so we can give you the best possible answer.

Cheers,

s46.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 17

Expert Comment

by:BillAn1
ID: 12075022
No, '' is not the same thing as NULL. You could pass in '', and use

SELECT * FROM MYTABLE WHERE ISNULL(MYFIELD, '') = %s

Also, be careful with your quotes. What are you using to call this piece of SQL? e.g. if this is a stored procedure

declare MyProc (@s)
AS
BEGIN
    SELECT * FROM MYTABLE WHERE ISNULL(MYFIELD, '') = @s
END

the correct way to call this would be
EXEC MyProc ''
OR
EXEC MyProc 'Sumting'

i.e. don't have any double quotes.
0
 

Author Comment

by:bganoush
ID: 12075184

I guess I really don't make myself clear enough...  The double quotes is just used to symbolize the string in a C++ context... The double-quotes never actually appear in the final SQL statement...

The actual SQL does NOT reside in a stored procedure... It is simply a SQL statement sent to the database server from within a C++ application.

so the actual SQL that MySQL or MSSQL receive look like this:

SELECT * from MYTABLE where MYFIELD=ISNULL(NULL,'')

or

SELECT * from MYTABLE where MYFIELD=ISNULL('single quoted string','')

With double-quotes, I just wanted to denote that the NULL is NOT surrounded by single quotes in the final statement but that the searched string DOES have single quotes surrounding it...  Is this mud clearer than my previous mud?

-- Bubba
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12075250
OK, I follow, but you need to do it backwards

SELECT * from MYTABLE where ISNULL(MYFIELD,'')=''

or

SELECT * from MYTABLE where ISNULL(MYFIELD,'')='single quoted string'

where ISNULL(MYFIELD,'')=''
means :
WHERE (IF MYFIELD IS NULL THEN REPLACE IT WITH '' OTHERWISE USE MYFIELD) = ''

so, if MYFIELD is NULL, then it will be returned. if MIFIELD is anything else (except ''), it will not be returned

where MYFIELD = ISNULL(NULL,'')
means WHERE MYFIELD = (IF NULL IS NULL THEN REPLACE IT WITH '')
since NULL IS NULL, this becomes
WHERE MYFIELD = ''
But, if MyFIELD is NULL, it will not be = '', so it will not be included.
0
 

Author Comment

by:bganoush
ID: 12075340

yes that's right but that's not what I want, I want this:

1) If my search string (not the field) is NULL, I don't want anything returned, hence, I use '' which doesn't exist in my table

2) If my search string contains something, then I want to search for it and get the values...

0
 

Author Comment

by:bganoush
ID: 12075351

Also, this is part of a bigger select statement with a union and some flag so it's pretty important NOT to skip the select when the search string is NULL

0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 250 total points
ID: 12075429
Ah, OK, then in that case, what you should do is

SELECT * from MYTABLE where MYFIELD =ISNULL(@s, MYFIELD)

that way, if @s is NULL, it will compare MYFIELD with MYFIELD, and so return the row - is that what you want?
from a performance point of view it will be a bit better to do


SELECT * from MYTABLE where ((@s IS NULL) OR (MYFIELD =@s))
0
 

Author Comment

by:bganoush
ID: 12076518
Not exactly,

if @s is NULL, I DON'T want to find any records for it. I only want to return values when @s contains something.

pseudo code:

    if (@s has content)
      select * from table where field = @s
    else if (@s doesn't have any content - i.e. NULL)
      don't select anything

I know that in C++, I could do:

   if (str != null)
     statement.execute ("select * from table wehre field = %s", mySearchString);

but this is a part of a larger select statement that I cannot pull apart...

The MSSQL version which is the original question works fine... (Because searching for '' returns an empty set)

   SELECT * from TABLE where FIELD=ISNULL(@s,'')

but I need to make a select statement to work in MySQL that doesn't have the "ISNULL".

-- BUBBA
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 12076543
the issue here is the difference between MySQL ISNULL and SQL SERVER ISNULL functions.

from MySQL Help:

ISNULL(expr)
If expr is NULL, ISNULL() returns 1, otherwise it returns 0. mysql> SELECT ISNULL(1+1);
        -> 0
mysql> SELECT ISNULL(1/0);
        -> 1

Note that a comparison of NULL values using = will always be false!


note that it is a boolean function not a string function so no search capability exists natively.

you should implement a pattern match query

Kaarthick's query would work for you, just make sure pass the wildcard '%' to the query
0
 

Author Comment

by:bganoush
ID: 12076824

Bill,

Thanks for the info, the last select you suggested was good except for the condition for the NULL:

SELECT * from MYTABLE where ((@s IS NOT NULL) AND (MYFIELD =@s))

-- Bubba
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now