Markus Blog

Switch Case in SQL

Usually when designing a database I often use an integer to save values that represent a string. To make it clear see table below:

Name	Role ID
Markus	1
Jose	2
Anonymous	3

On the table above I use integers to represent role id names, 1 = ‘Administrator’, 2 = ‘User’, 3 = ‘Guest’.

Because I use an integer as the role id, then on the application interface that I make, I need to convert it to a string ‘Administrator’ or ‘User’ or ‘Guest’. This can be done with a simple if else statement like the one below:

If (role == 1)
{
roleName = ‘Administrator’;
}
Else if (role == 2)
{
roleName = ‘User’;
}
Else
{
roleName = ‘Guest’;
}

Instead of using if else statement on the code, we can use SQL command that will do the same thing. To do this ‘if else’ command we can use ELT(N,str1,str2,str3,…) SQL command. ELT will return str1 if N = 1, str2 if N = 2, and so on. Here is the example of using ELT:

SELECT Name, ELT(RoleID, ‘Administrator’, ‘User’, ‘Anonymous’) AS Role FROM User;

The output data that we will get is like in the table below:

Name	Role
Markus	Administrator
Jose	User
Anonymous	Anonymous

The benefit of using ELT() is that we can make the data ready for presentation in the application interface without the need to make an if else code.

Markus