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?
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
In Oracle string conactenation is the || operator, not +, so try:

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

or

SET ORGANIZATION = 'IT&DA'


»bp
awking00Information Technology SpecialistCommented:
For Oracle, you can use the replace function -
set organization = replace(organization,chr(36),chr(38))
where organization = 'IT$DA';

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
thanks that did the trick.
Bill PrewIT / Software Engineering ConsultantCommented:
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) Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.