How to handle special character (such as ‘&’) in Oracle?

Following are the few options that could be tried to handle special characters in Oracle:-

Case Study 1

SELECT a.employee_id,
a.employee_code,
a.employee_display_name,
replace(a.employee_display_name,'&'||'reg;',NULL) updated_emp_display_name
FROM employee a
WHERE a.employee_id IN (26, 28, 48, 51, 61, 104, 39, 107);

Case Study 2

-- select ASCII('&') from dual
-- select CHR(38) from dual;

SELECT a.employee_id,
a.employee_code,
a.employee_display_name,
replace(a.employee_display_name,chr(38)||'reg;',NULL) updated_emp_display_name
FROM employee a
WHERE a.employee_id IN (26, 28, 48, 51, 61, 104, 39, 107);

Case Study 3

Set scan off
-- Setting the scan off tells sqlplus that dont scan for variables in the statements.

SELECT a.employee_id,
a.employee_code,
a.employee_display_name,
replace(a.employee_display_name,'®',NULL) updated_emp_display_name
FROM employee a
WHERE a.employee_id IN (26, 28, 48, 51, 61, 104, 39, 107);

Case Study 4

set define off

SELECT a.employee_id,
a.employee_code,
a.employee_display_name,
replace(a.employee_display_name,'®',NULL) updated_emp_display_name
FROM employee a
WHERE a.employee_id IN (26, 28, 48, 51, 61, 104, 39, 107);

set define on
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s