Oracle DECODE Function


/ Published in: SQL
Save to your folder(s)

A few examples of the Oracle DECODE function


Copy this code and paste it in your HTML
  1. DROP TABLE customers;
  2.  
  3. CREATE TABLE customers (
  4. first_name varchar2(100),
  5. last_name varchar2(100),
  6. country varchar2(20),
  7. employees NUMBER,
  8. start_date DATE
  9. );
  10.  
  11. INSERT INTO customers (first_name, last_name, country, employees, start_date) VALUES ('John', 'Smith', 'USA', 4, '12-APR-2010');
  12. INSERT INTO customers (first_name, last_name, country, employees, start_date) VALUES ('Sally', 'Jones', 'USA', 10, '04-JUL-2011');
  13. INSERT INTO customers (first_name, last_name, country, employees, start_date) VALUES ('Steve', 'Brown', 'Canada', 15, '21-MAR-2009');
  14. INSERT INTO customers (first_name, last_name, country, employees, start_date) VALUES ('Mark', 'Allan', 'UK', 23, '1-FEB-2001');
  15. INSERT INTO customers (first_name, last_name, country, employees, start_date) VALUES ('Adam', 'Cooper', 'USA', 55, NULL);
  16. INSERT INTO customers (first_name, last_name, country, employees, start_date) VALUES ('Josh', 'Thompson', NULL, 1, '10-FEB-2012');
  17. INSERT INTO customers (first_name, last_name, country, employees, start_date) VALUES ('Peter', 'Manson', 'France', NULL, '16-OCT-2012');
  18.  
  19.  
  20.  
  21. SELECT * FROM customers;
  22.  
  23.  
  24. /* Examples */
  25.  
  26. /* Example 1 */
  27.  
  28. SELECT first_name, country,
  29. DECODE(country, 'USA', 'North America') AS Decode_Result
  30. FROM customers;
  31.  
  32. /* Example 2 */
  33.  
  34. SELECT first_name, country,
  35. DECODE(country, 'USA', 'North America', 'UK', 'Europe') AS Decode_Result
  36. FROM customers;
  37.  
  38. /* Example 3 */
  39.  
  40. SELECT first_name, country,
  41. DECODE(country, 'USA', 'North America', 'UK', 'Europe', 'Other') AS Decode_Result
  42. FROM customers;
  43.  
  44. /* Example 4 */
  45.  
  46. SELECT first_name, country,
  47. DECODE(country, 'USA', 'North America', 'Canada', 'North America', 'UK', 'Europe', 'France', 'Europe', 'Other') AS Decode_Result
  48. FROM customers;
  49.  
  50. /* Example 5 */
  51.  
  52. SELECT first_name, employees,
  53. DECODE(employees, 1, 'Small') AS Decode_Result
  54. FROM customers;
  55.  
  56. /* Example 6 */
  57.  
  58. SELECT first_name, employees,
  59. DECODE(employees, 1, 'Small', 10, 'Medium', 50, 'Large', 'Unknown') AS Decode_Result
  60. FROM customers;
  61.  
  62. /* Example 7 */
  63.  
  64. SELECT first_name, country,
  65. DECODE(country, 'USA', 'North America', NULL, 'No Country') AS Decode_Result
  66. FROM customers;
  67.  
  68. /* Example 8 */
  69.  
  70. SELECT first_name, country
  71. FROM customers
  72. WHERE DECODE(country, 'USA', 'North America', 'Canada', 'North America', 'UK', 'Europe', 'France', 'Europe', 'Other') = 'North America';
  73.  
  74. /* Example 9 */
  75. SELECT first_name, country,
  76. DECODE(SIGN(INSTR(country, 'U')), 1, 'Found U', 0, 'Did not find U', 'Unsure') AS Decode_Result
  77. FROM customers;
  78.  
  79.  
  80. /* Example 10 */
  81. SELECT first_name, employees,
  82. DECODE(SIGN(20 - employees), 1, 'Less than 20', -1, 'Greater than 20', 'Unsure') AS Decode_Result
  83. FROM customers;
  84.  
  85. /* Example 11 */
  86.  
  87. SELECT first_name, last_name, country FROM customers;
  88.  
  89. UPDATE customers
  90. SET last_name = DECODE(country, 'USA', 'American', last_name);
  91.  
  92. SELECT first_name, last_name, country FROM customers;

URL: http://www.databasestar.com/oracle-decode-function/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.