Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's the difference between CASE and IF in mysql

Tags:

mysql

I found very strange MySQL behavior:

I have a string with umlaut letters, and I run some IF expression on it. Something like:

IF(length(field) < 10, '', replace(field, "\n", "<BR>"))

It it works fine.

However, if I replace this if by CASE, then the result is cut on the first unlaut letter!

CASE WHEN length(field)<10 THEN '' ELSE replace(field, "\n", "<BR>") END

Also, I noticed that it happens only when there is also GROUP BY part in the query.

I can't understand what's the difference between CASE and IF - from logical point of view both should return the same result exactly.

Anyone knows why the is difference between these two commands?

like image 848
diemacht Avatar asked Dec 04 '12 09:12

diemacht


1 Answers

"IF is a single fork, "CASE" can be multiple Use "Case" if you have more than two values optional values, "IF" when you have only two values.

General structure of CASE is:

CASE x
WHEN a THEN ..
WHEN b THEN ..
...
ELSE
END

General structure of IF:

IF (expr)
THEN...
ELSE...
END

So, basically IF is a CASE with only one 'WHEN' statement.

like image 92
Codesen Avatar answered Sep 28 '22 09:09

Codesen