Markus Blog What's going on Internet?

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:

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:

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

Most Commented Posts

• Squid Transparent Proxy Server on Windows Server 2003 (118)
• Installing Squid Cache for Windows (116)
• Installing VNC Remote Desktop on Ubuntu Linux VPS (60)
• Installing Squid NT on Windows 7 (27)
• Setting Nokia 2115i as Modem (25)