Big Data

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.

pinkypillsistock-533699494.jpg

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

cust_idlast_namefirst_namestate_codecitystatus
3233VADERDARTHNYNEW YORK1
2411SOLOHANMABOSTON2
7298FETTJANGOMIDETROIT2
6418LARSOWENTXPLANO1

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.

Syntax

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.

SELECT DECODE

('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

REGIONCUST_IDSTATUS
EAST32331
EAST24112
MIDWEST72982
SOUTH64181

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.

About Ant Pruitt

Ant Pruitt is an IT Support Professional with a passion for showing the non-geek how great technology can be. He writes for a variety of tech publications and hosts his own podcast. Ant is also an avid photographer and weight lifter.

Editor's Picks

Free Newsletters, In your Inbox