MySQL DECODE()

Introduction to MySQL DECODE()

The MySQL DECODE() function is known to be a decryption type of function in the Database for data interpretation. This MySQL DECODE function is responsible to decode an encrypted string part from the record values and then returns the original one. Also, if the input for this DECODE() function is provided as an empty encoded string then, the result processed by the MySQL will be the empty string itself. Otherwise, using the DECODE() function we can revert back the original string, which is now available in encoded form as per security purpose. The DECODE() function in MySQL allows the supplementing technique of if-then-else statement logic to the MySQL queries.

Syntax

Find the below MySQL syntax for DECODE() function:

DECODE(EncryptedString, PasswordString);

Explanation: From the above arrangement of MySQL DECODE() function, we can say that the function receives two parameters as arguments to execute the decoding process on the MySQL database. The first parameter ‘EncryptedString’ defines the encoded value string that is to be decoded and the second one i.e. ‘PasswordString’ is the password string that is useful to decode the input to produce the output string. The return value of this function is the original string which was encoded and stored in the database records. We use the DECODE function with SELECT MySQL Statement to execute the queries.

How does DECODE() function work in MySQL?

As per the DECODE() function syntax, there are two parameters used in the function for decoding the encoded value and generate the output. The initial argument is the string value that is to be searched or compared which is present in cryptic form and the last one is the key to decode the encoded string i.e. password.

This function performs to compare the argument expression specified to every search value line by line. In this processing, if the argument is found identical to a search, then the equivalent result is displayed by the MySQL database. If the match does not exist then, the function returns to default. If the default is by chance omitted then, MySQL server outputs NULL.

DECODE() function Arguments

The DECODE() function arguments can be as follows:

• Numeric Form: In this type of parameter value, we can include a Number, Binary_Float, or Binary_Double. If the first pair of the search result in the function is number, then the MySQL matches all the search result values and the initial argument value to find its maximum numeric antecedence, also converts the remaining parameters implicitly to the same data type and the result is produced in that specified data type.
• Character Form: In this type of form, the search and other argument are mentioned in character type where the MySQL DECODE function matches with the help of non-padded evaluation semantics. This form can include the inputs in any data types such as VARCHAR2, VARCHAR, NCHAR, CHAR, or, NVARCHAR2. Whatever, is the data type of the first search argument, the result data type character data type will be similar to it.
• When the first data type argument is in CHAR form or in a NULL value, the server results changes the output value to the VARCHAR2 data type.
• Since the DECODE function follows the if-the-else type of logical loop technique, so it uses a short series of evaluations like it examines search values just before matching it to the argument relatively than to all search results. Suppose, if the preceding search is identical to the argument then, the execution is completed.
• According to the first search value the data type of arguments will be converted and the result will also be displayed in the same data type in MySQL DECODE() function.
• The highest number of constituents that a DECODE() function can include is 255.

Examples to Implement MySQL DECODE()

We will now discuss how it works on the encoded data part:

Example #1

Executing MySQL DECODE() function using a string

Code:

SELECT DECODE(ENCODE('abc','abc@123'),'abc@123')

Output:

Example #2

Executing using a combination of characters and numbers

Code:

SELECT DECODE(ENCODE('education456','edu#321'), 'edu#321');

Output:

Code:

SELECT DECODE(ENCODE('Delhi#9807',''), '');

Output:

Explanation: We can also place the second argument in the DECODE() function as empty when we work on WAMP phpMyAdmin if the password is left empty or not set and if we set either on local or online database server then we need to add the password string.

Example #3

Executing using a NULL string value which returns the length of the same string after the process of compression

Code:

SELECT DECODE (ENCODE('NULL','password%123'),' password%123');

Output:

Explanation: In the above examples, it is clear that we the function ENCODE has encrypted the arguments using the passwords specified then, using the MySQL DECODE() function we now then are able to receive the original string or the first argument of the function with the help of the same password. If a character string is passed through the function then, the function returned a character data type result. In the case of combining both character and numeric, the result is also in the same format. Again, for NULL input the DECODE function produces NULL value itself.

Example #4

MySQL DECODE() function with table columns

Let us consider a sample table as ‘Test’ with fields ID, Name, and City where the Name column has encoded values. We have some data rows inserted into it. We have the table view as follows:

To execute DECODE() function in this table column like supposing Name then, the DECODE() function query statement with arguments are explained below:

Code:

SELECT Name, DECODE(Name,'') FROM test;

Output:

Explanation: From the above code, we come to know that the DECODE() function is applied to the table column ‘Name’, and as a result, the query has retrieved the decrypted data rows from the encoded ‘Name’ from the database.

Conclusion

Hence, if the DECODE() function on execution finds matches then fetches the original string but if no matches then, it outputs default but if the default is not there then, a NULL value is provided as result in MySQL. This DECODE() function is useful to undo the encrypted formula and receive the original data whenever necessary.

Recommended Articles

This is a guide to MySQL DECODE(). Here we discuss introduction to MySQL DECODE() function, with appropriate syntax, working, and examples for better understanding. You can also go through our other related articles to learn more –

1. MySQL IN Operator
2. ROLLUP in MySQL
3. MySQL Constraints
4. ANY in MySQL