Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1062
  • Last Modified:

What does ISNULL do exactly?

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
bganoush
Asked:
bganoush
  • 8
  • 4
  • 2
  • +3
1 Solution
 
KaarthickCommented:
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
 
BillAn1Commented:
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
 
KaarthickCommented:
You refine  your Query like the below one,

SELECT * FROM MYTABLE WHERE MYFIELD IS NULL OR myfield like 's%'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bganoushAuthor Commented:


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
 
bganoushAuthor Commented:

So Bill,

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

-- Bubba
0
 
Jay ToopsCommented:
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
 
solution46Commented:
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
 
bganoushAuthor Commented:


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
 
solution46Commented:
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
 
BillAn1Commented:
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
 
bganoushAuthor Commented:

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
 
BillAn1Commented:
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
 
bganoushAuthor Commented:

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
 
bganoushAuthor Commented:

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
 
BillAn1Commented:
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
 
bganoushAuthor Commented:
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
 
SoftEng007Commented:
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
 
bganoushAuthor Commented:

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 8
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now