Oracle CONCAT Function


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

A few examples of the CONCAT function, including setting up the example tables.


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.  
  20. SELECT * FROM customers;
  21.  
  22.  
  23. --Example 1
  24. SELECT CONCAT ('Complete ', 'IT Professional') AS NEW_CONCAT
  25. FROM DUAL;
  26.  
  27. --Example 2
  28. SELECT CONCAT (first_name, last_name) AS NEW_CONCAT
  29. FROM customers;
  30.  
  31. --Example 3
  32. SELECT CONCAT('My ', 'Résumé') AS NEW_CONCAT
  33. FROM DUAL;
  34.  
  35. --Example 4
  36. SELECT CONCAT(full_address, country) AS NEW_CONCAT
  37. FROM customers;
  38.  
  39. --Example 5
  40. SELECT first_name || last_name AS NEW_CONCAT
  41. FROM customers;
  42.  
  43. --Example 6
  44. SELECT CONCAT(CONCAT(first_name, last_name), country) AS NEW_CONCAT
  45. FROM customers;
  46.  
  47. --Example 7
  48. SELECT CONCAT(CONCAT(full_address, ', '), country) AS NEW_CONCAT
  49. FROM customers;
  50.  
  51. --Example 8
  52. SELECT CONCAT(CONCAT(CONCAT(CONCAT(first_name, ' '), last_name), ' - '), full_address) AS NEW_CONCAT
  53. FROM customers;
  54.  
  55. --Example 9
  56. SELECT first_name || ' ' || last_name || ' - ' || full_address AS NEW_CONCAT
  57. FROM customers;

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.