![]() It is a built-in function in ORACLE / PL SQL database management servers. The function is a close relative of CASE statements. In the above example, we have performed the following task using the DECODE function and have then ordered the result set by college_id.ĭECODE function is used to perform procedural IF-THEN-ELSE logic in SQL. SQL query to categories college fees into affordable and expensive for an Indian student, considering everything above $ 10000 as expensive.ĭECODE(fees,10000,'Affordable','Expensive') In the above example, we have performed the following IF-THEN-ELSE logic statements and then ordered the entire result set by college_id.Ĭode: IF college_name = 'Massachusetts Institute of Technology'ĮLSE IF college_name = 'California Institute of Technology' 'MIT','California Institute of Technology','CalTech','IIT') as college_name SELECT college_id, DECODE(college_name,'Massachusetts Institute of Technology', SQL query to illustrate abbreviation of college names based on the available data using DECODE function. Simple illustration of above mentioned DECODE function is as follows: In this example, we have performed a simple SQL task for categorizing colleges based on their location. Simple SQL query to illustrate use of DECODE function.ĭECODE (college_id, 10003,'Massachusetts, USA', The data in the “college_details” table after performing the above mentioned INSERT operations looks something as shown below: INSERT INTO college_details VALUES (10003, 'Massachusetts Institute of Technology', 'Massachusetts,India', 51520) select * from college_details INSERT INTO college_details VALUES (10004, 'California Institute of Technology', 'California ,USA', 60520) INSERT INTO college_details VALUES (10002, 'Indian Institute of Technology Bombay', 'Mumbai,India', 10000) We can use the following insert statements.Ĭode: INSERT INTO college_details VALUES (10001, 'Indian Institute of Technology Roorkee', 'Roorkee,India', 10000) Having created the table, let us now input some random data in it to work with in the subsequent exercises. We can use the following SQL CREATE TABLE statement to perform the task.Ĭollege_name character varying(255) NOT NULL,Ĭollege_location character varying(255) NOT NULL, Let us first create a ‘college_details’ table which contains college id, college name, location and fees for demonstration purposes. ![]() The simple illustration of the above mentioned decode function is as follows: ![]() The functionality of DECODE in ORACLE with following flowchart. And it finally converts back the data_type of result to the data_type of the expression. The DECODE function automatically converts or casts the expression to the data type of the first search argument or search_1. If it’s FALSE then DEFAULT value is returned. The first step is comparison of expression and search_1, if the expression = search_1 is TRUE then result_1 is returned. We can use the DECODE function as a part of the SELECT statement, ORDER BY etc. It is more or less like the ELSE statement in IF-THEN-ELSE. ![]() default: default argument holds the default value.For example, if expression = search_1 then result will be result_1. result_1, result_2, …, result_n: These arguments hold the result to be returned when the given comparison returns true.search_n: These are the values to be searched for and then compared with the expression argument. expression: expression argument is the value which is to be searched and compared with.So this may not work as shown here for very long input strings. Utl_encode.base64_decode('625746306147643165513D3D')) as decodedĮdit Please see Wernfried Domscheit's Comment below: the RAW data type is limited to 2000 bytes, or 32767 bytes with extended max string size. Let's see: select utl_encode.base64_encode(utl_raw.cast_to_raw('mathguy')) as encoded Then, to demonstrate the decoding step, I apply the BASE64_DECODE function to the output from the first query, and convert the resulting RAW value back to VARCHAR2 the result of the second query should be the input of the first. ![]() I start with the string 'mathguy' (in VARCHAR2 data type) first I convert it to RAW, and then I encode it to base64. Both functions work on RAW data type (both the input and the return value are RAW) so, if you want to use this for character data types, like VARCHAR2 or perhaps CLOB, you need to use additional conversions - as found in the UTL_RAW package functions like CAST_TO_RAW and CAST_TO_VARCHAR2. Oracle has the UTL_ENCODE package, including the BASE64_ENCODE and BASE64_DECODE functions. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |