Oracle CAST Function


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

A few examples of the Oracle CAST 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. full_address CLOB,
  8. employees NUMBER,
  9. start_date DATE
  10. );
  11.  
  12. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('John', 'Smith', 'USA', '10 Long Road', 4, '12-APR-2010');
  13. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Sally', 'Jones', 'USA', '50 Market Street', 10, '04-JUL-2011');
  14. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Steve', 'Brown', 'Canada', '201 Flinders Lane', 15, '21-MAR-2009');
  15. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Mark', 'Allan', 'UK', '8 Smith Street', 23, '1-FEB-2001');
  16. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Adam', 'Cooper', 'USA', '14 Wellington Road', 55, NULL);
  17. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Josh', 'Thompson', NULL, '80 Victoria Street', 1, '10-FEB-2012');
  18. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES ('Peter', 'Manson', 'France', '5 Johnson St', NULL, '16-OCT-2012');
  19. INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date) VALUES (NULL, '', NULL, '155 Long Road', NULL, '16-OCT-2012');
  20.  
  21. SELECT * FROM customers;
  22.  
  23.  
  24. --Example 1
  25.  
  26. SELECT '30-APRIL-2015',
  27. CAST('30-APRIL-2015' AS DATE) AS OUTPUT_VALUE
  28. FROM dual;
  29.  
  30. --Example 2
  31.  
  32. SELECT 41.522,
  33. CAST(41.522 AS CHAR(10)) AS OUTPUT_VALUE
  34. FROM dual;
  35.  
  36. --Example 3
  37.  
  38. SELECT ' 2093 ',
  39. CAST(' 2093 ' AS NUMBER) AS OUTPUT_VALUE
  40. FROM dual;
  41.  
  42. --Example 4
  43.  
  44. SELECT start_date,
  45. CAST(start_date AS TIMESTAMP) AS OUTPUT_VALUE
  46. FROM customers;
  47.  
  48. --Example 5
  49.  
  50. SELECT 9834,
  51. CAST(9834 AS VARCHAR2(30)) AS OUTPUT_VALUE
  52. FROM dual;
  53.  
  54. --Example 6
  55.  
  56.  
  57. SELECT first_name
  58. FROM customers
  59. WHERE country = 'USA';
  60.  
  61. CREATE TYPE usa_employee_first_names AS TABLE OF VARCHAR2(100);
  62.  
  63. SELECT
  64. CAST(MULTISET(SELECT first_name
  65. FROM customers
  66. WHERE country = 'USA')
  67. AS usa_employee_first_names) AS OUTPUT_VALUE
  68. FROM dual;

URL: http://www.databasestar.com/oracle-cast/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.