SQL refresher: How to use the DECODE function to produce user-friendly data

The DECODE function offers a handy way to make data easier to analyze and more understandable when you share it with others. Here's a quick review of how it works.


Image: iStock/Pinkypills

Ideally, your database will have all the data you need. But there are some scenarios where the data doesn't make much sense to the average user. Sometimes, it's just formatted in a way that's not user friendly. With SQL, you have a few options to display your data in a more understandible way. For this SQL refresher, I'd like to look at the DECODE function.

What is DECODE in SQL?

The DECODE function in SQL allows you to analyze data when certain conditions are met. Remember the CASE function we previously discussed? Think of DECODE as a shorthand version of CASE. Unfortunately, it's not available in SQL Server or MySQL. Users of those database types should refer to my previous article about using the CASE function. With DECODE, your SQL will essentially perform "if then" analysis, allowing you to display extracted data to the user in a more acceptable format.

SEE: Learn Fundamental SQL Programming With SQL Server (TechRepublic Academy)

Example scenario

In our example database, we'll work with Table A, a version of our the "customers" table used in the previous article. You'll notice that there are customers from various regions of the US in this table.

Table A: Customers


Let's analyze which regions some of the customers are from. The east, the midwest, the south? Knowing regions within the table could be useful data.


The syntax of the DECODE function is part of your SELECT statement. You put in your search criteria and what the result output should be if the data meets those criteria. In this example, I'd like to denote that the state of NY is to be shown as the EAST region in my output. I also want the state of MA to be shown as the EAST. I'll use the DECODE function and declare which column of the "customers" table I'd like to adjust by putting it in parenthesis, then follow it up with how I want the data to be referenced in the output. I"ll also be sure to use an alias for my DECODE column by adding an AS clause. In my case, I used AS region.


('state_code','NY', 'EAST', 'MA', 'EAST', 'MI', 'MIDWEST', 'TX', 'SOUTH') AS region,

cust_id, status

FROM customers;

Your output will display as follows:

Table B: Output


As you can see, I essentially completed an "IF this, THEN that" with the DECODE function. "If state_code = 'NY' THEN 'EAST' " is what the DECODE did in my SELECT statement. Pretty nifty, right?

The DECODE function works on Oracle and Informix databases. As mentioned, you can't use this function if you're running MySQL or Microsoft's SQL Server. Use the CASE function instead.

Also read...

Your SQL tips

Do you have any favorite SQL tips? Feel free to share them with your fellow TechRepublic members in the comments below. You can also tag me on Twitter with your advice and recommendations.