Usage of decode in sql
The DECODE function is used to evaluate different values based on conditional expressions, similar to the IF-ELSE syntax. Its usage includes: replacing NULL values, converting data types, and returning different values based on conditions.
Usage of DECODE function in SQL
Overview of DECODE function
## The #DECODE function is a SQL function that evaluates different values based on a conditional expression. It is similar to the IF-ELSE construct, but allows for a more concise syntax.Syntax
DECODE(expression, value1, result1, value2, result2, ..., default_result)
Parameters
- expression: The expression to evaluate.
- value1, value2, ...: The condition value to be checked.
- result1, result2, ...: The result value returned when matching the corresponding condition value.
- default_result: The default result value returned if the expression does not match any condition value.
Usage
DECODE function has the following usage:- Replace NULL value:You can use DECODE The function replaces the NULL value with the specified value, for example:
SELECT DECODE(name, NULL, 'Unknown', name) FROM customers;
- Convert data type: You can use the DECODE function to convert data from one type to another, For example:
SELECT DECODE(age, NULL, 0, age) FROM employees;
- Return different values according to conditions: You can use the DECODE function to return different values according to conditions, for example:
SELECT DECODE(gender, 'M', 'Male', 'F', 'Female', 'Other') FROM students;
Example
The following example demonstrates the use of the DECODE function:CREATE TABLE sales (
product_id INT,
product_name VARCHAR(50),
category VARCHAR(20),
sales_amount DECIMAL(10, 2)
);
INSERT INTO sales (product_id, product_name, category, sales_amount) VALUES
(1, 'Smart TV', 'Electronics', 2000.00),
(2, 'iPhone', 'Electronics', 1500.00),
(3, 'Coffee Maker', 'Appliances', 300.00),
(4, 'Microwave', 'Appliances', 450.00);
SELECT
product_name,
category,
DECODE(sales_amount,
NULL, 'No Sales',
2000.00, 'High Sales',
1500.00, 'Medium Sales',
'Low Sales') AS sales_category
FROM sales;Output:
<code>product_name | category | sales_category Smart TV | Electronics | High Sales iPhone | Electronics | Medium Sales Coffee Maker | Appliances | Low Sales Microwave | Appliances | Low Sales</code>
The above is the detailed content of Usage of decode in sql. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1389
52
iPhone 16 Pro and iPhone 16 Pro Max official with new cameras, A18 Pro SoC and larger screens
Sep 10, 2024 am 06:50 AM
Apple has finally lifted the covers off its new high-end iPhone models. The iPhone 16 Pro and iPhone 16 Pro Max now come with larger screens compared to their last-gen counterparts (6.3-in on the Pro, 6.9-in on Pro Max). They get an enhanced Apple A1
iPhone parts Activation Lock spotted in iOS 18 RC — may be Apple\'s latest blow to right to repair sold under the guise of user protection
Sep 14, 2024 am 06:29 AM
Earlier this year, Apple announced that it would be expanding its Activation Lock feature to iPhone components. This effectively links individual iPhone components, like the battery, display, FaceID assembly, and camera hardware to an iCloud account,
iPhone parts Activation Lock may be Apple\'s latest blow to right to repair sold under the guise of user protection
Sep 13, 2024 pm 06:17 PM
Earlier this year, Apple announced that it would be expanding its Activation Lock feature to iPhone components. This effectively links individual iPhone components, like the battery, display, FaceID assembly, and camera hardware to an iCloud account,
Gate.io trading platform official app download and installation address
Feb 13, 2025 pm 07:33 PM
This article details the steps to register and download the latest app on the official website of Gate.io. First, the registration process is introduced, including filling in the registration information, verifying the email/mobile phone number, and completing the registration. Secondly, it explains how to download the Gate.io App on iOS devices and Android devices. Finally, security tips are emphasized, such as verifying the authenticity of the official website, enabling two-step verification, and being alert to phishing risks to ensure the safety of user accounts and assets.
Multiple iPhone 16 Pro users report touchscreen freezing issues, possibly linked to palm rejection sensitivity
Sep 23, 2024 pm 06:18 PM
If you've already gotten your hands on a device from the Apple's iPhone 16 lineup — more specifically, the 16 Pro/Pro Max — chances are you've recently faced some kind of issue with the touchscreen. The silver lining is that you're not alone—reports
Anbi app official download v2.96.2 latest version installation Anbi official Android version
Mar 04, 2025 pm 01:06 PM
Binance App official installation steps: Android needs to visit the official website to find the download link, choose the Android version to download and install; iOS search for "Binance" on the App Store. All should pay attention to the agreement through official channels.
Beats adds phone cases to its lineup: unveils a MagSafe case for the iPhone 16 series
Sep 11, 2024 pm 03:33 PM
Beats is known for launching audio products such as Bluetooth speakers and headphones, but in what can best be described as a surprise, the Apple-owned company has branched into making phone cases, starting with the iPhone 16 series. The Beats iPhone
How to solve the problem of 'Undefined array key 'sign'' error when calling Alipay EasySDK using PHP?
Mar 31, 2025 pm 11:51 PM
Problem Description When calling Alipay EasySDK using PHP, after filling in the parameters according to the official code, an error message was reported during operation: "Undefined...
