String concation

When we want to join expressions or columns together, we need to use concatenation operator which is ||. Here is some example:

select 'The name from employee with '|| employee_id || ' is '||last_name||' '||first_name from employees;

'THENAMEFROMEMPLOYEEWITH'||EMPLOYEE_ID||'IS'||LAST_NAME||''||FIRST_NAME
-------------------------------------------------------------------------
The name from employee with 198 is OConnell Donald
The name from employee with 199 is Grant Douglas
The name from employee with 200 is Whalen Jennifer
The name from employee with 201 is Hartstein Michael
The name from employee with 202 is Fay Pat
The name from employee with 203 is Mavris Susan
The name from employee with 204 is Baer Hermann
The name from employee with 205 is Higgins Shelley
The name from employee with 206 is Gietz William
...

What is happaning with the string that contains a quotation mark? Well, in that case we have 2 opportunities:

1. Adding an edditional quotation mark.

Example:

select 'My systers''s son is called Jemy' as Additional_quotation from dual;

ADDITIONAL_QUOTATION
-------------------------------
My systers's son is called Jemy


  2.   Using Q-quote operators

select q'(my sister's son is called Jamy)' from dual;

Q'(MYSISTER'SSONISCALLEDJAMY)'
------------------------------
my sister's son is called Jamy

Instead of brackets we can use can use any character we want. For example

select Q'K my syster's son is called Jamy K' from dual;
select Q'# my syster's son is called Jamy #' from dual;
select Q'% my syster's son is called Jamy %' from dual;

All those statements will produce the same result:
my sister's son is called Jamy