asked on

Proper syntax to include a special character in a sql statement.

UPDATE_structure_code
SET ORGANIZATION = 'IT'+'&'+'DA'
WHERE ORGANIZATION = 'IT$DA';

I am getting error on the set statement - what is the proper syntax to include the ampersand in my text?

Bill Prew

In Oracle string conactenation is the || operator, not +, so try:

SET ORGANIZATION = 'IT'||'&'||'DA'

or

SET ORGANIZATION = 'IT&DA'

»bp

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

thanks that did the trick.

Bill Prew

Did my comment not work for you, should have, I might have expected at least an assist. But that's your call.

For what it's worth, you can use those characters in your particular query as they are, no need to escape or use CHR() as far as I know, so it can be this to keep it more clear...

UPDATE structure_code SET ORGANIZATION = REPLACE(ORGANIZATION, '$', '&') WHERE ORGANIZATION = 'IT$DA';

Open in new window

Glad you got a solution, good luck.

»bp

slightwv (䄆 Netminder)

Depending on the tool you are using to execute the update statement, you might nee to do an extra step before you can use '&'. In many Oracle tools the ampersand is taken to be a runtime substitution variable.

Normally by using: set define off

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqpug/SET-system-variable-summary.html#GUID-9EE759CA-77B0-4ACA-BE24-DDCB2B910E1B