I want to replace '&' character with '&' in SQL

 

I want to replace '&' character with '&' in SQL

This is special requirnment this result they want to use for there deployment .please find all ddl and dml commands with my trials

CREATE TABLE my_table (
  my_column VARCHAR2(100)
);

INSERT INTO my_table (my_column) VALUES ('Apples & Bananas');

SELECT REPLACE(my_column, '&', '&') AS modified_column
FROM my_table;

SELECT REPLACE(my_column, CHR(38), '&') AS modified_column
FROM my_table;

Please suugest a sql query which will give result

Output I am getting:

MY_COLUMN
Apples & Bananas

Output I want:

MODIFIED_COLUMN
Apples & Bananas

3 Answers

Your target character & and replacement & are the same thing. The replacement should be &:
SELECT REPLACE(my_column, '&', '&') AS modified_column
FROM my_table;
See live demo.
But really you shouldn't be doing HTML escaping this way. Rather, use a programming language which has a proper HTML escaping function, such as JavaScript or Python.


Have you tried this:
SELECT REPLACE(my_column, '&', '&') AS modified_column FROM my_table;
as your current and replace characters are same '&'. So, use '&' instead.


replace should work OK.
SQL> set define off
SQL> CREATE TABLE my_table
  2  (
  3     my_column   VARCHAR2 (100)
  4  );

Table created.

SQL> INSERT INTO my_table (my_column)
  2       VALUES ('Apples & Bananas');

1 row created.

SQL> SELECT * FROM my_table;

MY_COLUMN
--------------------------------------------------------------------------------
Apples & Bananas

SQL> UPDATE my_table
  2     SET my_column = REPLACE (my_column, '&', '&');

1 row updated.

SQL> SELECT * FROM my_table;

MY_COLUMN
--------------------------------------------------------------------------------
Apples & Bananas

SQL>

• sql
• database
• oracle
• replace
• oracle-sqldeveloper