# CASE

Switch statement construct. Evaluates the specified expression/condition and returns the result from the matching expression.

## Syntax

```
(CASE
  WHEN condition THEN val1
  WHEN condition THEN val2
  ...
  ELSE defaultval END)

```

Switching off a single expression:

```
(CASE expression
  WHEN case1 THEN val1
  WHEN case2 THEN val2
  ...
  ELSE defaultval END)

```

## Arguments

* Any SQL objects

## Return Type

The return type depends on the type of arguments `val1`, `val2`, etc. For example, if `val1` or `val2` is a string, the return type will be a string, depending on which condition is met. If they are both integers, the return type will be an integer, and so on.

## Remarks

* The expressions inside `CASE` statements are evaluated sequentially, and the evaluation stops with the first matching condition. If there are multiple matching conditions, the value of the first matching `WHEN` clause is returned.
* `CASE` statements have two formats:

  * The first format uses a collection of independent `WHEN` conditions, and it returns the result from the first `WHEN` condition that returns `TRUE`.
  * The second format uses only one expression or condition, and it returns the results from the first `WHEN` clause which satisfies the condition.
* In case none of the `WHEN` clauses evaluate to the `CASE` expression or return `TRUE`, the `CASE` statement returns the expression specified in the `ELSE` clause.

## Examples

## Example 1

The following example shows how to use a `CASE` statement using only one condition.

```sql
CREATE TABLE allviews (State varchar(10), Product varchar(5), Views int);
INSERT INTO allviews VALUES
("CA","E",50), 
("NY","C",15), 
("NY","F",40), 
("CA","A",60), 
("CA","D",10), 
("NY","B",20), 
(NULL,"G",40);

```

```sql
SELECT * FROM allviews;

```

```output

+-------+---------+-------+
| State | Product | Views |
+-------+---------+-------+
| CA    | E       |    50 |
| NY    | C       |    15 |
| NY    | F       |    40 |
| CA    | A       |    60 |
| CA    | D       |    10 |
| NY    | B       |    20 |
| NULL  | G       |    40 |
+-------+---------+-------+
```

```sql
SELECT Views,
(CASE State
  WHEN 'CA' THEN 'California'
  WHEN 'NY' THEN 'New York'
  ELSE 'N/A'
  END) As 'State'
FROM allviews;

```

```output

+-------+------------+
| Views | State      |
+-------+------------+
|    20 | New York   |
|    50 | California |
|    10 | California |
|    60 | California |
|    15 | New York   |
|    40 | N/A        |
|    40 | New York   |
+-------+------------+

```

## Example 2

The following example shows how to use `CASE` statements with independent `WHEN` conditions.

```sql
SELECT
( CASE
  WHEN 1=0 THEN 3
  WHEN 0=1 THEN 5
  ELSE 8
  END) AS 'Choices';

```

```output

+---------+
| Choices |
+---------+
|       8 |
+---------+

```

## Example 3

The following example shows that the value of only the first matching `WHEN` clause is returned.

```sql
SELECT
( CASE 'ohai'
  WHEN 'yo' THEN 'sup'
  WHEN 'hello' THEN 'hi'
  WHEN 'ohai' THEN 'bai'
  WHEN 'ohai' THEN 'ok'
  ELSE 'huh?'
  END) As 'Greeting';

```

```output

+----------+
| Greeting |
+----------+
| bai      |
+----------+

```

> **⚠️ Warning**: ## Implicit CollationWhen `character_set_server` is set to `utf8`, string literals with characters using 4-byte encoding are implicitly assigned binary collation and processed as a sequence of bytes rather than characters. This implicit conversion to binary collation causes string functions to return unexpected results. To avoid using implicit binary collation, either use explicit type casting or use database columns defined with the `utf8mb4` character set. For more information, refer to [Implicit Collation in Special Cases](https://docs.singlestore.com/cloud/reference/sql-reference/character-encoding/special-cases.md).

***

Modified at: February 24, 2023

Source: [/cloud/reference/sql-reference/conditional-functions/case/](https://docs.singlestore.com/cloud/reference/sql-reference/conditional-functions/case/)

(An index of the documentation is available at /llms.txt)