CODEWITHSUNDEEP
sqloracle11g
contactmatt
contactmatt

Reputation: 18620

Escaping special characters in SQL

Is there an easy way in Oracle to escape special characters in a SQL statement? (i.e. %, &, ') I saw this link in regard to manually escaping characters, but I thought Oracle may have provided an easier way to do so.

Note: I'm generating dynamic SQL select statements through an ORM.

Upvotes: 7

Views: 30143

Answers (3)

guzzibill
guzzibill

Reputation: 81

here's the definitive answer page on tech on the net. Even provides examples and exercises

http://www.techonthenet.com/sql/like.php

Upvotes: 0

Adam Musch
Adam Musch

Reputation: 13628

If using bind variables and ORM, embedded single quotes and ampersands should be handed automatically; those are special characters in SQL*Plus or SQL*Developer.

To use LIKE where looking for the literal characters % and _ (not their multi- and single-character wildcard versions), you'd use the escape clause of the like condition:

select * from my_table where some_text like '/%%' escape '/';

will only return the rows where some_text begins with a percent sign.

Upvotes: 16

Eduardo
Eduardo

Reputation: 33

It seems you're looking for something like the command SET DEFINE OFF, which you can run and it affects the whole SQL session. This command, however, only prevents Oracle from giving special meaning to the ampersand character. It doesn't affect other special characters such as the single quote.

A couple of links to additional information regarding escaping characters follow:

https://forums.oracle.com/forums/thread.jspa?threadID=2256637

http://docs.oracle.com/cd/B10501_01/text.920/a96518/cqspcl.htm

Upvotes: 2

Related Questions