Questions tagged [case]

In SQL, the CASE expression evaluates a list of conditions, returning the result for the first condition which evaluates to true. For programming languages' CASE questions, use the [switch-statement] tag instead.

The CASE expression is an SQL construction used to return different values depending on specified conditions. There are two forms of the CASE expression. The first is the simple CASE:

CASE search_expr
WHEN compare_expr1 THEN result_expr1
WHEN compare_expr2 THEN result_expr2
ELSE else_expr
END

This will compare search_expr to each compare_expr in order until it finds a condition where search_expr = compar_expr, and return the result_expr of that condition. If no such condition is found the else_expr is returned.

The second form of CASE expression is the searched CASE:

CASE 
WHEN boolean_expr1 THEN result_expr1
WHEN boolean_expr2 THEN result_expr2
ELSE else_expr
END

This will test boolean_expr in order until it finds a condition where the boolean_expr is true, and return the result_expr of that condition. If no such condition is found the else_expr is returned.

There are two short forms for special CASE expressions; COALESCE and NULLIF.

COALESCE(x1, x2, ..., xn) 

is equivalent to:

CASE WHEN x1 IS NOT NULL THEN x1 
     WHEN x2 IS NOT NULL THEN x2
     ...
     ELSE xn
END 

I.e. the COALESCE expression returns the value of the first non-null operand, found by working from left to right, or null if all the operands equal null.

NULLIF(x1, x2) 

is equivalent to

CASE WHEN x1 = x2 THEN NULL ELSE x1 END

I.e. if the operands are equal, NULLIF returns null, otherwise the value of the first operand.

8090 questions
58
votes
4 answers

What is the reason for the weird syntax of the "case" statement in a bash/zsh script?

Looking from a programmer's point of view then shell script is just another programming language, where one has to learn and conform to the rules of the language. However, I have to admit that this syntax is the weirdest style I have ever seen in a…
phunehehe
  • 8,618
  • 7
  • 49
  • 79
58
votes
6 answers

PHP switch case more than one value in the case

I have a variable that holds the values 'Weekly', 'Monthly', 'Quarterly', and 'Annual', and I have another variable that holds the values from 1 to 10. switch ($var2) { case 1: $var3 = 'Weekly'; break; case 2: $var3 =…
Alex Pliutau
  • 21,392
  • 27
  • 113
  • 143
58
votes
8 answers

Expression inside switch case statement

I'm trying to create a switch statement but I can't seem to be able to use an expression that gets evaluated (rather than a set string/integer). I can easily do this with if statements but case should hopefully be faster. I'm trying the…
Marko
  • 71,361
  • 28
  • 124
  • 158
55
votes
4 answers

MySQL Case in Select Statement with LIKE operator

Is it possible to combine the CASE statement and the LIKE operator in a MySQL SELECT statement? For Example, I am trying to query a database that stores data in a single column in either one of two formats (this is awful and hurts my head, but I…
HurnsMobile
  • 4,341
  • 3
  • 27
  • 39
54
votes
2 answers

CASE statement in SQLite query

Why this query doesn't work? :( I tried to replace nested IF statement "...SET lkey = IF(lkey >= 11, lkey - 5, IF(lkey > 5, lkey + 2,lkey))" UPDATE pages SET lkey = CASE lkey WHEN lkey >= 11 THEN lkey - 5 ELSE CASE lkey WHEN lkey…
VeroLom
  • 3,856
  • 9
  • 34
  • 48
46
votes
7 answers

C# Switch-case string starting with

Is there any way to make a case condition in a switch statement where you say if a string begins with something? ex Switch (mystring) { case("abc")://String begins with abc (abcd or abc1 or abcz or abc.. or abc will fall in this condition). …
Amra
  • 24,780
  • 27
  • 82
  • 92
46
votes
1 answer

Case Expression vs Case Statement

What is the difference between a Case Expression and a Case Statement in MySQL? When can they be used, and what are the benefits of using one over the other? Case Statement syntax: CASE WHEN search_condition THEN statement_list [WHEN…
mrmryb
  • 1,479
  • 1
  • 12
  • 18
44
votes
5 answers

Convert string to Pascal Case (aka UpperCamelCase) in Javascript

Id like to know how I can covert a string into a pascal case string in javascript (& most probally regex). Conversion Examples: double-barrel = Double-Barrel DOUBLE-BARREL = Double-Barrel DoUbLE-BaRRel = Double-Barrel double barrel = Double…
Blowsie
  • 40,239
  • 15
  • 88
  • 108
44
votes
3 answers

TSQL CASE with if comparison in SELECT statement

I would like to use CASE statement in SELECT. I select from user table, and (as one attribute) I also use nested SQL: SELECT registrationDate, (SELECT COUNT(*) FROM Articles WHERE userId = Users.userId) as articleNumber, hobbies,…
CyberHawk
  • 958
  • 2
  • 11
  • 27
44
votes
4 answers

Regex to change to sentence case

I'm using Notepad++ to do some text replacement in a 5453-row language file. The format of the file's rows is: variable.name = Variable Value Over Here, that''s for sure, Really Double apostrophe is intentional. I need to convert the value to…
jkramp
  • 491
  • 1
  • 5
  • 8
43
votes
3 answers

Laravel Eloquent Select CASE?

Is there anyone with experience in PHP & Laravel Eloquent who can help me resolve this statement? I'm trying to inject a CASE... WHEN.. END... inside a raw() method. It seemed like it was completely ignored. The existing documentation hasn't been .…
Coach Roebuck
  • 904
  • 2
  • 12
  • 20
42
votes
5 answers

SELECT query with CASE condition and SUM()

I'm currently using these sql statements. My table has the field CPaymentType which contains "Cash" or "Check". I can sum up the amount of payments by executing 2 SQL statements as shown below. In this case, the user won't even notice the speed…
chris_techno25
  • 2,401
  • 5
  • 20
  • 32
40
votes
2 answers

check for null date in CASE statement, where have I gone wrong?

My source table looks like this Id StartDate 1 (null) 2 12/12/2009 3 10/10/2009 I want to create a select statement, that selects the above, but also has an additional column to display a varchar if the date is not null such as :…
Jimmy
  • 16,123
  • 39
  • 133
  • 213
36
votes
3 answers

Execution order of WHEN clauses in a CASE statement

Given the following body of a case statement: 1 WHEN r.code= '00' then 'A1' 2 WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2' < 3 WHEN r.code ='0120' then 'A3' 4 WHEN r.code ='01' …
The Ghost
  • 681
  • 1
  • 6
  • 15
35
votes
2 answers

Using CASE in PostgreSQL to affect multiple columns at once

I have a Postgres SELECT statement with these expressions: ,CASE WHEN (rtp.team_id = rtp.sub_team_id) THEN 'testing' ELSE TRIM(rtd2.team_name) END AS testing_testing ,CASE WHEN (rtp.team_id = rtp.sub_team_id) THEN 'test example' ELSE…
Elliot B.
  • 17,060
  • 10
  • 80
  • 101