OraFAQ Forum: SQL & PL/SQL » The ESCAPE Option

Home » SQL & PL/SQL » SQL & PL/SQL » The ESCAPE Option

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

The ESCAPE Option [message #681062]

Wed, 10 June 2020 06:28

amirmg
Messages: 4
Registered: June 2020

Junior Member

I cannot understand the last line of the following code. Could you pleas let me know what is result of the following code?
SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id LIKE ’%SA\_%’ ESCAPE ’\’;

I also need the format of the ESCAPE code.

Thanks,
Amir

Report message to a moderator

Re: The ESCAPE Option [message #681063 is a reply to message #681062]

Wed, 10 June 2020 07:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: The ESCAPE Option [message #681064 is a reply to message #681062]

Wed, 10 June 2020 07:29

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

SQL Language Reference, LIKE.

Report message to a moderator

Re: The ESCAPE Option [message #681070 is a reply to message #681064]

Wed, 10 June 2020 13:29

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Typically an underscore if a single character wildcard. However the escape clause gives you the ability to actually use the underscore or "%"

SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id LIKE '%SA\_%' ESCAPE '\';

where this means it to look for a job_id that contains the string "<any characters>SA_<ANY CHARACTERS>"

Without the ESCAPE you would be searching for "<any characters>SA<any single character><ANY CHARACTERS>"

Report message to a moderator

Re: The ESCAPE Option [message #681077 is a reply to message #681063]

Fri, 12 June 2020 01:10

amirmg
Messages: 4
Registered: June 2020

Junior Member

Dear BlackSwan,

Thank you so much for your valuable links.
This was my first post here, and I was not familiar with the rules.

Regards,
Amir

Report message to a moderator

Re: The ESCAPE Option [message #681078 is a reply to message #681064]

Fri, 12 June 2020 01:13

amirmg
Messages: 4
Registered: June 2020

Junior Member

Morning Michel,

Thank you for your care answering my question, and I also followed the link you sent me, but regrettably I cannot access Oracle website owing to to the imposed sanction to my country.

Regards,
Amir

Report message to a moderator

Re: The ESCAPE Option [message #681079 is a reply to message #681070]

Fri, 12 June 2020 01:16

amirmg
Messages: 4
Registered: June 2020

Junior Member

Dear Bill B,

You answered my question completely. Thank you ever so much.

Regards,
Amir

Report message to a moderator

Re: The ESCAPE Option [message #681081 is a reply to message #681078]

Fri, 12 June 2020 02:29

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

amirmg wrote on Fri, 12 June 2020 08:13

Morning Michel,

Thank you for your care answering my question, and I also followed the link you sent me, but regrettably I cannot access Oracle website owing to to the imposed sanction to my country.

Regards,
Amir

The question is now how could you have a product you should not. Wink

Report message to a moderator

Previous Topic:	Time Portion of Date/Time Value Being Stripped When Passed to Stored Procedure (3 merged)
Next Topic:	get closer to datetime in two tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 18 06:47:05 CST 2026