CODEWITHSUNDEEP
sqlmysqldatabasems-access
Joshery
Joshery

Reputation: 345

What is equivalent of the Nz Function in MS Access in MySQL? Is Nz a SQL standard?

What is MySQL equivalent of the Nz Function in Microsoft Access? Is Nz a SQL standard?

In Access, the Nz function lets you return a value when a variant is null. Source

The syntax for the Nz function is:

Nz ( variant, [ value_if_null ] )

Upvotes: 11

Views: 27008

Answers (5)

Bill Karwin
Bill Karwin

Reputation: 562731

The COALESCE() function does what you describe. It's standard SQL and it should be supported in all SQL databases.

The IFNULL() function is not standard SQL. Only some brands of databases support this function.

Upvotes: 16

Mike Wills
Mike Wills

Reputation: 21275

You might want to look at IFNULL or COALESCE. If I recall correctly, IFNULL works for MySQL.

Upvotes: 3

minalud
minalud

Reputation: 11

Have look to the null safe operator <=>

Maybe it could help : http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_equal-to

Upvotes: 0

diamondsea
diamondsea

Reputation: 3080

COALESCE does just what the OP is asking for, as does IFNULL:

SELECT Nz(MightBeNullVar, 0) FROM ...  (MS Access version)
SELECT COALESCE(MightBeNullVar, 0) FROM ... (MySQL version)
SELECT IFNULL(MightBeNullVar, 0) FROM ... (MySQL version)

The difference is the COALESCE can search through multiple variables and return the first non-null one:

SELECT COALESCE(MightBeNullVar, MightAlsoBeNullVar, CouldBeNullVar, 0) FROM ... (MySQL version)

each of these will return a 0 (zero) if none of the values have a set value (are null).

The IFNULL is (pretty meaninglessly) faster. There's probably other better things to optimize in your query before bothering with IFNULL vs COALESCE issues. If you have multiple things to check, use COALESCE. If you only have a single value to check, use IFNULL.

Upvotes: 4

Dan Tupper
Dan Tupper

Reputation: 9

Perhaps Knowing what MS Access NZ() Function actually does would be helpful (prior to answering with completely invalid suggestions). The NZ() Function test for Null and Replaces the Null with an empty string, a Zero or optionally a value that the user enters.

COALESCE doesn't even come close, in fact it returns a Null if no none Null values in a 'List???'

IFNULL() Function is what you're looking for.

http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html

Upvotes: -1

Related Questions