OraFAQ Forum: SQL & PL/SQL » SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL - Escape character

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

SQL - Escape character [message #345866]

Fri, 05 September 2008 02:53

ygsunilkumar
Messages: 100
Registered: August 2008

Senior Member

Pls go thru the attachment & provide me the correct query to pull out the '.' symbol associated qualification.

Attachment: sql escape.txt
(Size: 1.14KB, Downloaded 892 times)

Report message to a moderator

Re: SQL - Escape character [message #345870 is a reply to message #345866]

Fri, 05 September 2008 03:09

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It must be me (as I don't know what you, actually, need) but, if you'd want to find people whose "qualification" column value contains a dot ".", you could use INSTR function as

select * from your_table
where instr(qualification, '.') > 0

Report message to a moderator

Re: SQL - Escape character [message #345873 is a reply to message #345870]

Fri, 05 September 2008 03:18

ygsunilkumar
Messages: 100
Registered: August 2008

Senior Member

Thanks a lot! it's working but actually i need to pull out the qualification column values where '.' symbol is associated using escape character. It's working for other symbols('_', '%', '/' etc) but not for '.' dot. Pls give me the query using escape character.

Report message to a moderator

Re: SQL - Escape character [message #345875 is a reply to message #345873]

Fri, 05 September 2008 03:28

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

Senior Member
Account Moderator

Explain in detail possibly with an example what you need, it is unclear.

Regards
Michel

Report message to a moderator

Re: SQL - Escape character [message #345880 is a reply to message #345875]

Fri, 05 September 2008 03:35

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Quote:

It's working for other symbols('_', '%', '/' etc) but not for '.' dot

Post It.

Quote:

Pls give me the query using escape character.

Post a test Case Create Table And Insert Statements.

Regards,
Rajat

Report message to a moderator

Re: SQL - Escape character [message #346090 is a reply to message #345875]

Fri, 05 September 2008 20:06

ygsunilkumar
Messages: 100
Registered: August 2008

Senior Member

I have attached text file(with example)..pls go thru that hope it will help and give me correct query to pull out the data.

Attachment: sql escape.txt
(Size: 1.81KB, Downloaded 958 times)

Report message to a moderator

Re: SQL - Escape character [message #346101 is a reply to message #346090]

Sat, 06 September 2008 00:46

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

Senior Member
Account Moderator

You don't need to escape '.', it is not a joker character for Oracle.

Regards
Michel

Report message to a moderator

Re: SQL - Escape character [message #346103 is a reply to message #346101]

Sat, 06 September 2008 00:51

ygsunilkumar
Messages: 100
Registered: August 2008

Senior Member

Then suggest me the correct query and explain in detail. what do you mean by this?...i didn't understand (You don't need to escape '.', it is not a joker character for Oracle.)

Report message to a moderator

Re: SQL - Escape character [message #346108 is a reply to message #346103]

Sat, 06 September 2008 01:15

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

Senior Member
Account Moderator

'.' is treated in the same way than say 'A'.

Regards
Michel

Report message to a moderator

Re: SQL - Escape character [message #346111 is a reply to message #346108]

Sat, 06 September 2008 01:24

ygsunilkumar
Messages: 100
Registered: August 2008

Senior Member

Thanks a lot. now i got it..it working!!! Smile

Report message to a moderator

Re: SQL - Escape character [message #395290 is a reply to message #346108]

Wed, 01 April 2009 01:35

ygsunilkumar
Messages: 100
Registered: August 2008

Senior Member

'.' is treated in the same way than say 'A'.

I am satisfied with the above answer but I have doubt why Oracle is considering '.' as character and not as symbol such as '_', '%', '/'. Is that an Oracle Bug or is there any specific reason?

Report message to a moderator

Re: SQL - Escape character [message #395292 is a reply to message #395290]

Wed, 01 April 2009 01:42

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

Senior Member
Account Moderator

'_', '%' are the only wild characters for LIKE operator and so they are the only ones you need to espace (with the escape character itself).
You don't need to and can't escape '/' (unless it is the escape character you choose).

Regards
Michel

Report message to a moderator

Re: SQL - Escape character [message #395296 is a reply to message #395292]

Wed, 01 April 2009 01:50

ygsunilkumar
Messages: 100
Registered: August 2008

Senior Member

Thanks but why Oracle is considering '.' as character?

Report message to a moderator

Re: SQL - Escape character [message #395302 is a reply to message #395296]

Wed, 01 April 2009 02:00

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

Senior Member
Account Moderator

Because it is a character as well as / , ; and other special characters (but it is not an alphabetical character).

Regards
Michel

Report message to a moderator

Re: SQL - Escape character [message #395317 is a reply to message #395302]

Wed, 01 April 2009 02:25

ygsunilkumar
Messages: 100
Registered: August 2008

Senior Member

Sorry, i didnot understand. Can you please explain in brief?

Report message to a moderator

Re: SQL - Escape character [message #395324 is a reply to message #395317]

Wed, 01 April 2009 02:34

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

Senior Member
Account Moderator

Sorry but I don't understand what you don't understand.
All what is in a character set is a character?
Among these, they are printable characters and not printable characters.
Among the former ones they are alphabetical characters, digits and other characters which can be special characters and not special characters depending on the usage. For instance '_' is a special character for LIKE, it is not for an identifier. At the opposite '.' is not a special character for LIKE, it is for an identifier.

Regards
Michel

Report message to a moderator

Re: SQL - Escape character [message #395345 is a reply to message #395324]

Wed, 01 April 2009 03:31

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I strongly suspect that you did not read the text of the error message that you got:

ORA-01424:	missing or illegal character following the escape character
Cause:	The character following the escape character in LIKE pattern is missing or not one of the escape character, '%', or '_'.
Action:	Remove the escape character or specify the missing character.

I think that makes the cause of your problem pretty clear.

Report message to a moderator

Previous Topic:	How to process every line of a table
Next Topic:	Extract Data within the Parenthesis

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 03:37:25 CST 2025