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

5 thoughts on “Switch Case in SQL”

  1. Quite nice, but what happens if roleID starts -for example- at 4? Or you want to start it at 4, because you do not need the 1,2,3? Can you exend the ELT() statement with that?

Leave a Reply

Your email address will not be published. Required fields are marked *